Categoría en #Flujos de Datos

Dataflows admite cuadro de dialogo en la consulta nativa de SQL pero provocando de momento un error crítico si usas parámetros

Lo que esta mañana parecía una grandísima noticia, la llegada de los cuadros de diálogo en las consultas nativas de SQL en dataflows se ha convertido en una auténtica pesadilla para mi.

Está ya en manos del equipo de soporte de Microsoft (que por cierto no tenían ha nadie para atenderme en castellano y ha sido difícil hacerles entender mi problema) y esperemos una pronta solución, pero de momento he encontrado un atajo para parchearlo, aunque me queda un día duro de trabajo cambiando queries.

EL PROBLEMA

Esta mañana me encontraba con una importante novedad que justo ayer en un webinar comentaba que no entendía que no hubieran solucionado, no existía cuadro de diálogo para las consultas nativas en SQL y por fin ya lo hay:

Sin embargo, empezaban a aparecerme correos en mi bandeja de actualizaciones de dataflows que habían fallado esta misma mañana en el proyecto en el que trabajo. Chequeo y algunas consultas nativas habían desaparecido.

Me pongo a profundizar en el problema y no habían desaparecido todas, únicamente aquellas que contenían parámetros.

Si introduces una consulta nativa en el cuadro de dialogo, éste no admite parámetros, pero si podías incluir los parámetros manualmente en M de esta forma:

En el desktop, se rompe el cuadro de diálogo pero se traga el parámetro sin problema

¿Qué pasa en el nuevo cuadro de diálogo en los flujos de datos? Pues simplemente que no rompe el cuadro de diálogo sino que desaparece la Consulta Nativa:

Introduces el parámetro en el código M
La consulta nativa desaparece porque prevalece el cuadro de diálogo que no se rompe no admitiendo el parámetro

Podemos concluir, por tanto que de momento las consultas nativas en dataflows no admiten parámetros.

Solución provisional

Mientras que el equipo de soporte lo soluciona, si lo soluciona, no me queda más remedio que hacer lo siguiente:

a) Convierto la consulta con la query nativa en una función

b) Me traigo el parámetro en un Origen, lo convierto en tabla e invoco la función creada sobre el parámetro

Es una chapuza que de momento em funciona, porque la otra opción es dejar el parámetro fijo y cambiarlo manualmente cada día.

No sé si soy la única persona del mundo a este nivel de uso de dataflows pero para mi ha sido un verdadero problema crítico en mi proyecto

Evitar las entidades calculadas cuando utilizas un flujo de datos como origen de otro flujo de datos.

Mediante este menú podemos agregar entidades vinculadas. Reza en su menú: “La vinculación a entidades de otros flujos de datos reduce la duplicación y ayuda a mantener la coherencia en toda la organización.”

Si pinchamos en él nos lleva directamente a conectar un flujo de datos en un nuevo flujo de datos con el mismo conector que usamos para conectar un flujo de datos a un dataset

Nos logueamos con la cuenta de nuestra organización y podemos conectarnos a otro flujo de datos

Pero nos indica el siguiente mensaje:

Las entidades vinculadas necesitan de una capacidad premium.

Sin embargo, siguiendo la misma premisa de la combinación y la anexión en los flujos de datos que podéis ver en uno de mis post antiguos:

Hoy descubrí casi por casualidad que si deshabilitamos la carga de la entidad vinculada y la referenciamos, la nueva entidad copia de la entidad vinculada deshabilitada no es una entidad vinculada:

Como podemos ver no da el warning de entidad vinculada

El nuevo dataflow actualiza sin problema evitando la capacidad Premium.

Control total de las actualizaciones de nuestros datasets y dataflows con Power Automate

La actualización de datasets y dataflows en el servicio de power bi pueden programarse, con el límite de 8 al día en el servicio de Power BI para que se lleven a cabo a una hora concreta en intervalos de 30 minutos. Sin embargo en determinados escenarios podemos necesitar que la hora de inicio de la actualización sea dinámica, dependa de un evento anterior. Imaginad por ejemplo que trabajamos sobre un modelo de datos que ataca un DataWarehouse, o datos en una capa de staging que se almacenan en un proceso que se realiza cada noche y que puede no tener una hora de conclusión fija.

Por otro lado, cuando actualizamos un dataflow necesitamos actualizar con posterioridad todos los datasets que dependen de él y esto es aun más complicado porque no podemos saber de manera alguna actualmente cuando ha acabado la actualización de ese dataset.

Todas estas casuísticas podemos solucionarlas haciendo depender la actualización no de la programación en el servicio, demasiado estática, sino creando el mapa de actualizaciones en powerautomate.

En el siguiente video os muestro como hacerlo.

Integración de varios archivos con la misma estructura desde una carpeta de sharepoint en dataflows.

Solución de problemas e introducción a la creación de funciones en Power Query

Siempre digo que soy un #dataflow_lover. Todos mis proyectos intento desarrollarlos mediante flujo de datos aunque en ocasiones las cosas se ponen complicadas debido a algunos errores que pueden aparecer. En las últimas semanas, la integración de carpetas con sharepoint está dando algún quebradero de cabeza. Vamos a analizar uno concreto, la integración de archivos con una misma estructura desde una carpeta de sharepoint.

Para empezar, en cuanto creamos un dataflow y elegimos Carpeta de Sharepoint como Origen de datos nos encontramos con el primer error. La secuencia es la siguiente:

Escogemos Carpeta de Sharepoint como origen de datos
Introducimos la URL (ya sabeis que tiene que ser el directorio Raíz, sin la carpeta Documentos) e iniciamos sesión con nuestra cuenta de Microsoft (al ser un origen Cloud no necesitamos gateway)

Y lo primero que nos encontramos es que la carpeta aparece vacía.

Esto es muy sencillo de resolver. El conector del Dataflow no está escribiendo correctamente la sentencia y basta con añadir al código lo siguiente:

SharePoint.Files("https://quolutions.sharepoint.com/sites/quolutions", [ApiVersion = 15])
Con [ApiVerion]=15 primer problema resuelto

Vamos a escoger sólo dos archivos de la carpeta para el ejemplo:

Le damos a las flechitas para combinar y nos devuelve un error

Antes de crear las funciones de combinación de manera automática como el desktop, el primer paso de filtrado de archivos ocultos devuelve un error.

Esto no pasa en el desktop que va a mostrar el cuadro de diálogo de combinación de archivos y va a crear las funciones necesarias después de aceptar:

¿Cómo podemos resolver esto en Dataflow? No he encontrado documentación por ningún sitio. Incluso hay un hilo en la comunidad sin resolver: https://community.powerbi.com/t5/Service/quot-Unexpected-error-quot-when-reading-sharepoint-folder-full/td-p/841277

Como yo lo resuelvo es simplemente creando yo la función de combinación. De hecho incluso en el desktop, no me gusta la forma en que Power Query realiza la automatización de la función y suelo crear yo mi propia función. El procedimiento es sencillo.

1º Escojo sólo un archivo con el que trabajar las transformaciones, hago doble click sobre binary en uno de ellos y me devuelve la información del csv en este caso. Valdría igual con excel o cualquier otro origen.

El código que me genera es el siguiente:

let
  Origen = SharePoint.Files("https://quolutions.sharepoint.com/sites/quolutions", [ApiVersion = 15]),
  Navegación = Origen{[Name = "LOG_20200513.csv", #"Folder Path" = "https://quolutions.sharepoint.com/sites/quolutions/Documentos compartidos/General/Argomaniz/DATOS_VISION/"]}[Content],
  #"CSV importado" = Csv.Document(Navegación, [Delimiter = ";", Columns = 17, QuoteStyle = QuoteStyle.None]),
  #"Encabezados promovidos" = Table.PromoteHeaders(#"CSV importado", [PromoteAllScalars = true]),
  #"Tipo de columna cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos", {{"FECHA", Int64.Type}, {"HORA", type text}, {"LADO", type text}, {"A1", Int64.Type}, {"A2", Int64.Type}, {"R1_A", Int64.Type}, {"R1_B", Int64.Type}, {"R2", Int64.Type}, {"R3", Int64.Type}, {"R4", Int64.Type}, {"R5", Int64.Type}, {"R6", Int64.Type}, {"B1", Int64.Type}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", Int64.Type}, {"RESULTADO", type text}})
in
  #"Tipo de columna cambiado"

Ahora lo que quiero es simplemente crear una función que tenga como parámetro el nombre del archivo. Para crear la función, tengo que envolver todo el código anterior en un let …. in en el que creo el parámetro y sustituir en el código anterior el nombre del archivo por el parámetro. El código de la función quedaría así:

let
  archivos = (nombrearchivo as text) =>  //este es el parámetro creado



let
  Origen = SharePoint.Files("https://quolutions.sharepoint.com/sites/quolutions", [ApiVersion = 15]),
  Navegación = Origen{[Name = nombrearchivo, #"Folder Path" = "https://quolutions.sharepoint.com/sites/quolutions/Documentos compartidos/General/Argomaniz/DATOS_VISION/"]}[Content], //aquí cambiamos el nombre del archivo por el parámetro
  #"CSV importado" = Csv.Document(Navegación, [Delimiter = ";", Columns = 17, QuoteStyle = QuoteStyle.None]),
  #"Encabezados promovidos" = Table.PromoteHeaders(#"CSV importado", [PromoteAllScalars = true]),
  #"Tipo de columna cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos", {{"FECHA", Int64.Type}, {"HORA", type text}, {"LADO", type text}, {"A1", Int64.Type}, {"A2", Int64.Type}, {"R1_A", Int64.Type}, {"R1_B", Int64.Type}, {"R2", Int64.Type}, {"R3", Int64.Type}, {"R4", Int64.Type}, {"R5", Int64.Type}, {"R6", Int64.Type}, {"B1", Int64.Type}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", Int64.Type}, {"RESULTADO", type text}})
in
  #"Tipo de columna cambiado"
in
  archivos
Y ya tenemos nuestra función creada

No os lo había advertido pero lo mejor es crear la función sobre una copia de la consulta antes de hacer las transformaciones de la tabla porque ahora sobre ese archivo original es sobre el que invocaremos la función.

Vamos a quedarnos para este ejemplo con 3 logs unicamente

Nos quedamos sólo con la columna name e invocamos la función que previamente habíamos creado.

En este punto, el usuario si no tiene mucha experiencia puede entrar en pánico porque esto no existe en los dataflow

Pero a poco que conozcamos un poco como funciona M, la invocación de una función no es más que agregar una columna personalizada y eso si podemos hacerlo en los dataflow y en la fórmula pondremos:

Nombrefunción([ColumnaParametro])

Mi función se llama consulta y la columna que quiero invocar en el parámetro es el nombre del archivo (name)

Le damos a aceptar y ya nos trae cada una de las tablas con todas las transformaciones que hayamos hecho. En el ejemplo no he hecho ninguna transformación pero podríamos hacer las que quisiéramos. Sólo tenemos que expandir la tabla y listo.

Espero que sea de utilidad y que estas pequeñas piedras en el camino no os aparten de la senda de los Dataflow

Exportar un dataflow

Vamos a detallar los pasos a seguir para exportar un dataflow entre dos tenants diferentes o dentro de un mismo tenant entre dos espacios de trabajo diferentes, el caso es el mismo.

En este caso se trata de un dataflow cuyas conexiones son todas on cloud, por lo que no reguieren puerta de enlace.

En primer lugar hay que exportar el archivo .json que contiene todas las tablas del dataflow:

En segundo lugar, nos vamos al nuevo espacio de trabajo donde queremos importar el dataflow, le damos a crear dataflow y en la pantalla de creación le damos a Importar Modelo:

Seleccionamos el archivo .json exportado anteriormente y directamente nos va a crear el nuevo flujo de datos:

Editamos las credenciales y ya lo tenemos listo, así de simple.

Actualizamos el flujo de datos y así carga la información de las entidades.

Si en el dataflow tenemos alguna query nativa, nos dará un mensaje de error como el siguiente:

Para salvar este error es sencillo. Abrimos el archivo .json con un editor como por ejemplo Notepad++ y le decimos que busque y reemplace la siguiente cadena:

Pero solo debemos reemplazarla cuando venga predecida de “AllowNativeQuery”, el resto de trues que nos encontremos no hay que cambiarlos.

De esta forma ya se carga correctamente:

Con estos sencillos pasos podremos exportar e importar nuestros flujos de datos según lo necesitemos.

Gran batería de novedades (+50) en los flujos de datos

Hace unos días Miguel Llopis anunciaba en el blog de Microsoft Power BI una batería de más de 50 novedades en los Flujos de Datos de este mes.

https://powerbi.microsoft.com/en-us/blog/50-new-features-in-dataflows-this-month/

Tengo pendiente explicaros detenidamente qué es y por qué utilizo en mi día a día un flujo de datos, pero de momento, aquí os dejo una explicación de todas las novedades que han introducido en la actualización del servicio en Noviembre.

Uno de los problemas que quienes hemos elegido los flujos de datos como herramienta principal de autoservicio de ETL nos hemos encontrado desde el principio ha sido la limitación que en el power query del servicio de power bi existía en cuanto al número de transformaciones programadas en la interface.

Esto lo han mejorado muchísimo en esta actualización con + de 50 nuevas transformaciones disponibles.

Quedan otras en el camino, que seguramente acabarán llegando hasta equiparar la automatización de la interface de power query en la nube con la interface de power query en el desktop, pero el paso que han dado es importantísimo.

Paso a detallar las novedades más importantes que me he encontrado y que echaba muchísimo en falta en la anterior versión de los dataflow:

1º Nuevos conectores a orígenes de datos

Este es el mapa de todos los orígenes que actualmente presentan los flujos de datos y he recuadrado en nuevo los 7 nuevos orígenes de esta actualización:

2º Nuevas transformaciones de datos

Todas las trasformaciones que voy a describir en este punto nos parecerán obvias su existencia a todos los que trabajamos ya con power query en power bi desktop, pero hasta ahora no estaban en la interface del power query de los flujos de datos. Reitero, no obstante, que el hecho de que no existiera en la interface la manera de hacerlo automáticamente no significa que el motor de power query no identificase el código M que se genera en esas transformaciones. Si realizábamos dichas transformaciones directamente escribiendo la línea de código en M o copiando y pegando desde power bi desktop, podíamos realizar las transformaciones sin problema alguno. No obstante, con esta actualización el uso directo de la interface de power query de los flujos de datos se simplifica y se refuerza, de manera que ya no será tan necesario, como hacíamos hasta ahora, general el código M en el desktop y copiarlo en los flujos de datos.

2.1 Combinar archivos

Algo tan sencillo como combinar automáticamente dos Excel con la misma estructura ubicados en una misma carpeta de sharepoint o local, que se realiza de manera automática habitualmente en el desktop, ahora es también posible desde los flujos de datos.

Elegimos como origen de datos una carpeta local en este caso:

Al hacer login(recordad que necesitamos una puerta de enlace instalada y conectada que tenga acceso a la carpeta) nos muestra todos los archivos que encuentra en la carpeta, en este caso dos Excel con la contabilidad de dos años diferentes pero con la misma estructura.

Le damos a transformar datos y una vez en el editor vamos a  …/combinar tablas /combinar archivos.

Le decimos qué archivo queremos que tome de ejemplo (o dejamos Primer archivo), seleccionamos el libro del Excel que queremos combinar y damos a aceptar. Power query se encarga de montar toda la estructura de parámetros, funciones y el resultado final combinado, igual que en el desktop.

2.2 Combinar consultas. Selección de tipo de unión visual.

Combinar consultas presenta ahora una descripción visual mediante diagrama de Venn para que el usuario no familiarizado con el uso de términos tales como inner join, left join, etc pueda escoger el tipo de relación que quiere establecer.

2.3 Transformaciones de número, fecha, fecha/hora a través de la barra de herramientas

Algo tan sencillo como este tipo de transformaciones que conocemos sobradamente de power query en power bi desktop no existía antes de esta actualización y debía hacerse utilizando código M directamente en la consulta.

2.4 Reducir filas, rellenar, mover

Funciones a las que estamos tan acostumbrados en power query para power bi desktop no estaban implementadas en la interface de power query en los flujos de datos, por lo que el salto en la realización de transformaciones mediante la barra de herramientas es impresionante.

Hasta ahora, las limitaciones eran tantas que salía más rentable realizar las transformaciones en el power bi desktop y copiar el código en una consulta en blanco. Con esta actualización y el gran avance que supone, un porcentaje muy alto de las transformaciones habituales está, por fin, cubierto.

3º Data profiling

Al igual que en el desktop, los flujos de datos incorporan ahora el perfil de columna y, lo mismo que en el desktop lo podemos configurar basado en las 1000 primeras filas o en el total de filas del conjunto de datos.

Lo podemos habilitar o deshabilitar en Opciones/Opciones globales.

Mi primera conclusión en su utilización es que es extremadamente lento en la carga, sobre todo si se activan todas las opciones.

Aquí os lo muestro sólo con las dos primeras pestañas seleccionadas.

Y aquí con todas las pestañas marcadas:

4º Creación de parámetros

Hasta ahora no existía en la interface la posibilidad de crear parámetros mediante un cuadro de diálogo. Ojo, esto no significa que no se pudieran utilizar. Yo los he utilizado sin problemas. Lo que significa es que no podías configurarlos mediante un cuadro de diálogo en la interface ni tenías un cuadro de diálogo para cambiar el parámetro, había que hacerlo manualmente en la línea de código. Antes, simplemente copiando el código de un parámetro de power bi desktop en una consulta en blanco del flujo de datos, tenías el parámetro listo para usarlo, pero con las limitaciones descritas de no poder modificarlo en un cuadro de diálogo.

Ahora ya existe en la interface un cuadro de diálogo para la creación y modificación de parámetros tal y como lo tenemos en el power bi desktop.

5º Creación de funciones

Tras esta actualización pueden crearse muy fácilmente funciones a partir de consultas que contengan algún parámetro

Y la probamos invocando las cuentas que sean del tipo GASTO:

6º Intellisense

Algo que nos parece ya tan normal como el Intellisense es totalmente nuevo en los flujos de datos.

7º Seleccionar tablas relacionadas

Otra característica que estamos acostumbrados a utilizar, la posibilidad de traernos al modelo tablas relacionadas del origen de datos, es también una novedad en los flujos de datos.

Aunque todo lo que ha llegado a los flujos de datos parecerán para los usuarios que manejan habitualmente power bi desktop cosas obvias y necesarias, no estaban hasta ahora en los flujos de datos, por lo que la manera óptima de realizar transformaciones en los flujos era o bien picar código M si se tenían los conocimientos adecuados, o bien crear la consulta en power bi desktop y copiarla y pegarla en el flujo de datos.

Con esta batería de más de 50 funcionalidades, ya casi se puede hacer todas las transformaciones que habitualmente realizamos en el desktop directamente en los flujos de datos. Echo en falta algo que esperaba en una actualización así, como la opción de agregar una columna, ya que en los flujos de datos de momento sólo se puede agregar una columna condicional o una columna de índice. Y por otro lado, ya que han incluido los parámetros y las funciones, no estaría mal poder agregar columna a través de la invocación de una función, funcionalidades estas que estoy seguro que llegarán en próximas actualizaciones.