Parámetros y Funciones en Power BI / Power Query – Combinando Archivos

  • Facebook
  • Twitter
  • LinkedIn

Te recomiendo que leas la primera y segunda parte de esta serie de entradas antes de leer ésta, pero puedes comenzar a leer si ya entiendes los conceptos de parámetros, argumentos y funciones del lenguaje M dentro de Power Query / Power BI.

Comprender la experiencia de Combinar Archivos: un ejemplo práctico

Consolidación
  • Facebook
  • Twitter
  • LinkedIn

El caso que vamos a abordar es LA forma más común en la que la gente usa las Funciones sin siquiera saber lo que sucede dentro de Power Query / Power BI.

Vamos a examinar la nueva experiencia de Combinar Archivos que fue introducida en Power BI Desktop al inicio del 2017 y la forma en que todo cambió desde entonces.

Intentaremos combinar 3 archivos que tenemos dentro de una carpeta. Cada archivo es un csv sencillo con datos mensuales e intentaremos adjuntar o consolidar todo en una única tabla.

Puedes seguir los pasos si descargas los archivos de muestra usados en este ejemplo haciendo clic en el siguiente botón:

Descarga los archivos de ejemplo

¡Hagámoslo!

Conectar a la carpeta

Asegúrate de descomprimir los archivos que has descargado antes de continuar y recuerda en donde guardaste la carpeta o archivos.

El primer paso es conectarnos a esa carpeta que acabamos de descomprimir.

Selecciona la opción “Desde una carpeta” o «From Folder» en Power BI Desktop o Power Query dentro de Excel:

Esto abrirá una nueva ventana donde ingresarás la ruta de la carpeta:

Después de hacer clic en Aceptar u OK, una nueva ventana aparecerá que muestra una vista previa de los archivos dentro de la carpeta. En lugar de hacer clic en “Combinar”, haz clic en Editar o Transformar si estás dentro de Power BI Desktop:

Esto nos llevará al Power Query Editor donde comenzaremos a hacer un poco de Magia:

En esta parte es donde la gente normalmente hace clic en el botón Combinar archivos o Combine Files, pero en este caso vamos a hacerlo completamente manual para que comprendas mejor lo que sucede tras bambalinas cuando haces clic en ese botón y la forma en la cual se logra usando únicamente funciones y parámetros.

Combinar Archivos
  • Facebook
  • Twitter
  • LinkedIn

(¡No hagas clic en ese botón! ¡Sigue leyendo este articulo!)

Escoge un archivo de muestra

Los datos dentro de cada uno de nuestros archivos se ven así:

Esto requiere un conjunto de transformaciones, pero en lugar de definir las transformaciones en cada uno de los archivos, podemos crear algo que se llama Sample file y luego crear una función desde ese archivo de muestra que luego puede ser aplicado en cada uno de los archivos dentro de nuestra carpeta.

Puede sonar como una tarea complicada, pero de hecho es bastante sencilla.

Lo que haremos es seguir una de las técnicas de navegación que expliqué en este artículo.

Lo único que debemos hacer es hacer clic en uno de los archivos (El texto que dice binario en verde o amarillo) que tenemos en nuestra carpeta y usarlo como nuestro archivo de muestra.

No olvides seleccionar la opción “Agregar como una consulta nueva” o «Add as New Query». El resultado de esa operación será una nueva consulta con un único Binario:

Recomiendo que renombres la consulta como Sample File Binary.

Crear el parámetro

Hasta el momento tenemos 2 consultas. Una es una tabla de los archivos dentro de nuestra carpeta y la otra es únicamente un binario de uno de los archivos en la carpeta.

El siguiente paso es crear un Parámetro. Para lograrlo, iremos a la Pestaña de Inicio, hacemos clic en Administrar Parámetros y agregamos un nuevo Parámetro como se muestra a continuación:

Crear una Función

Con nuestro parámetro ya creado, el siguiente paso es crear la función Configurada o Definida por el usuario.

Comenzaremos creando una referencia sobre el archivo Sample File Binary:

Y en la consulta que acabamos de crear, cambiaremos la fórmula en la barra de fórmulas de = #»Sample File Binary»=#”Sample File Parameter”

Al presionar enter, Power Query intentará actuar automáticamente y el resultado se verá así:

Voy a cambiar el nombre de esta consulta de Sample File Binary (2) a Transform Sample File.

Ahora lo que necesitamos hacer es clic derecho en nuestra consulta y seleccionamos la opción Crear función…:

Esto desplegará otra ventana donde podemos definir el nombre de nuestra nueva función:

Escogí Transform File Function y al hacer clic en Aceptar aparece lo siguiente:

Aún no terminamos. Ahora debemos crear nuestra función, pero no tenemos que escribir ni una sola línea de código para esto.

Puedes regresar a la consulta Transform Sample File y comenzar a hacer las transformaciones desde allí.

Únicamente necesitamos hacer 3 Transformaciones, pero antes de hacer cualquiera de ellas, recomiendo eliminar el paso Cambiar tipo. Escribir a puro código el nombre de las columnas puede crear algunos errores en el futuro y esa es la razón principal por la cual nos deshacemos del paso Cambiar tipo en esta etapa.

Reemplazar los valores

Nota como en la primera columna, que se llama Month, tenemos varios valores vacíos.

Para reemplazar esos con el nombre del mes, debemos reemplazar los valores nulos con valores en blanco haciendo clic derecho en la columna, seleccionamos la opción Reemplazar valores e ingresamos los valores a continuación:

Luego puedes hacer un Rellenar abajo en esa columna:

Unpivot o Anular dinamización

Vemos que los mercados están a través de todas las columnas, pero necesitamos colocarlos todos en una sola columna llamada “Market” o “Region”. Esto nos indica que debemos anular la dinamización a las columnas. Para eso, simplemente debemos seleccionar las 2 columnas a las que no queremos quitar la dinamización (Month y Product Name), hacemos clic derecho en ellas y hacemos la operación Anulación de dinamización de otras columnas o Unpivot Other Columns:

Renombrar las columnas

Después de realizar todos los pasos anteriores, terminamos con una tabla de 4 columnas, pero necesitamos cambiar los nombres de las columnas de Attribute a Region y de Values a Amount:

Puede que no lo veas, pero todo lo que acabas de hacer ha sido traducido en una pieza de código en lenguaje M y puesto en nuestra Transform Sample File.

Organizar todo en carpetas / grupos

Antes de continuar quiero dejar todo organizado, entonces voy a colocar todo en Carpetas / Grupos:

Este es un paso opcional, pero agrega mucho valor cuando quieres regresar a tu consulta y ver como se entrelazan cada una de las partes. Crear un grupo es tan sencillo como hacer clic derecho en el panel Consultas y seleccionar la opción Crear un grupo o mover una consulta de un grupo a otro.

Aplicar la función a los demás archivos

¡Muy bien! Estamos listos. Lo último que debemos hacer ahora es aplicar esta función a todos los archivos dentro de la consulta Monthly Sales Data. ¿Cómo se hace eso?

Solamente debes ir a esa consulta, hacer clic en el menú Añadir columna y seleccionar la opción que dice Invocar función personalizada o Invoke Custom Function:

Esto creará una nueva columna y eliminaré el resto de columnas de mi consulta para quedarme únicamente con esa nueva columna y el nombre del archivo. Luego, hago clic en el botón Expandir en la nueva columna y expando todas las columnas dentro de la tabla:

Luego de configurar los tipos de datos para cada columna, pude hacer una prueba y asegurarme que toda la información sí estaba dentro de la tabla:

Ahora tienes una mejor comprensión de lo que sucede cuando haces clic en el botón Combinar archivos y lo poderosas que son las Funciones.

Extensibilidad

Lo bueno de este procedimiento es que es bastante amigable para el usuario. Puedes agregar nuevos parámetros a tu función y el código se ajustará a lo que necesites.

Puedes agregar nuevos pasos, quitar los anteriores o cambiarlos desde la consulta Transform Sample File y la función se ajustará automáticamente sin que tengas que escribir nada de código.

Por ejemplo, podría agregar un nuevo parámetro llamado TopRows:

Voy a mi consulta Transform Sample File y agrego un nuevo paso a Keep Top Rows, o Mantener las Primeras Filas (usando ese nuevo Parámetro como argumento):

Y cuando regreso a mi consulta Monthly Sales Data, me mostrará un error porque la Función requiere 2 parámetros y solamente pasamos 1.

Esto significa que el Diálogo mostrará 2 parámetros en lugar de 1. Aquí está lo que hice con eso:

Lo que significa que solamente tendré una fila (la primera fila de la tabla) de cada archivo.

Se verá así:

¿Por qué este procedimiento y no el procedimiento manual?

Si eres un usuario avanzado en lenguaje M, entonces podrías hacer todo esto al escribir todo el código en ese lenguaje, pero la experiencia de depuración de errores en cualquier otro procedimiento que no sea el que describí aquí simplemente no es efectivo, ya que consume un 80% de tu tiempo al tener que ir y regresar entre el código escrito y la ejecución del mismo.

Con el procedimiento que acabo de describir en esta entrada, puedes tener el código en una consulta y una vista previa de cómo funciona en otra, lo que es en mi experiencia, invaluable cuando tratas de comprender un código que no creaste por ti mismo. Sin mencionar que Power Query crea la función de manera automática.

Es por eso que recomiendo este procedimiento incluso para los profesionales más experimentados en lenguaje M. Esta es por MUCHO una mejor experiencia cuando tratas de depurar errores y comprender CUALQUIER código que CUALQUIER otra persona haya escrito.

Permanece en sintonía de nuestro blog, la siguiente semana cubriremos el tema de Parameter Tables (Tablas de Parámetros).

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *