Saludos ¿Listo para algo de diversión con Power Query?
Apuesto a que si, Power Query es genial, aqui vamos:
Uno de los problemas más comunes con los que tenemos que lidiar para crear poderosos reportes en Excel, es el de relacionar múltiples tablas o bases de datos que provienen de orígenes diferentes.
Afortunadamente para nosotros, existe Power Query 😀
En el pasado, debíamos recurrir a funciones, procesos manuales largos o código VBA para completar la tarea efectivamente.
Te quiero recomendar dos caminos para «Entretejer «tablas en Excel, que te ahorrarán mucho tiempo y harán tu trabajo más productivo:
El primero de ellos, es a través de Power Pivot y podrás ver el video en el siguiente enlace:
• Relacionar tablas con Power Pivot
Y el segundo camino, es a través de Power Query, una alternativa bien útil y que resulta muy productiva, así que empecemos con el ejemplo…
– Ejemplo : Tienda de Postres
Imagina que tienes el mejor trabajo del mundo en una Tienda de Postres y a partir de ahora te han solicitado crear un reporte con las ventas de las diferentes sucursales del día anterior, todo enfocado en la creación de estrategias que ayuden a aumentar los ingresos de la tienda en general.
Para llevar a cabo esta misión, te han proporcionado el primer archivo de texto con toda la información de las ventas consolidada, sin detallar la sucursal en la cual se realizó la venta.
Como puedes observar, el archivo no cuenta con el formato tabular apropiado:
Observa las dos primeras líneas, datos que no necesitamos, ¿y si son múltiples archivo?, ¿la limpieza sería manual?, ¿qué pasa si la tare es de todos los meses?:
Noooooooooooooooooooo!!! ……
¡Don’t Panic! – Power Query Esta Aquí para Ayudarnos
Debemos tener en cuenta que en el consolidado de ventas no se detalla ninguna información sobre los productos vendidos en cada transacción, y en consecuencia , también, se nos ha suministrado la siguiente tabla estructurada en un archivo de Excel.
Para analizar no solo las unidades vendidas desde diferentes perspectivas, sino otras variables de negocio, debemos relacionar las dos tablas a través de Power Query por lo que vamos a empezar con nuestros famoso proceso Extract – Transform – Load en Power Query.
Solución
Archivo – Consolidado de Ventas
1. Importar Tabla a Power Query (Extract)
Primero, debemos importar los datos consolidados de las ventas y crear una nueva consulta
En un nuevo libro de Excel -> Pestaña Power Query -> Grupo Obtener Datos Externos -> Desde un Archivo -> Desde Texto -> En el cuadro de dialogo examinar elegimos la ubicación del archivo de texto -> damos clic en Aceptar.
Automáticamente, aparece el editor de consultas con los datos del archivo conectados.
Como podrás observar, debemos transformar y limpiar la tabla … ¡Power Query Esta Aquí para Ayudarnos, recuerdas! 😉
2. Transformación en Power Query (Transform)
Primero: Quitar columnas Innecesarias.
Clic derecho sobre la columna que contiene todos los datos -> en la lista desplegable que aparece Quitar otras columnas.
Segundo: Eliminar primera columna
Clic izquierdo sobre la etiqueta de la columna -> Pestaña Inicio -> comando Reducir Filas -> opción Quitar Filas -> Quitar Filas Superiores -> y en el cuadro de dialogo Quitar Filas Superiores, indicamos el npumero de filas de la parte superior que deseamos quitar, para este caso una solamente -> clic en Aceptar.
Tercero: Dividir la Columna en varias columnas según los datos del archivo de texto
Con la columna de los datos seleccionada -> Pestaña Inicio -> grupo Transformar -> clic en el comando Dividir Columna -> y elegimos la opción Por Delimitador.
Automáticamente aparece el cuadro de dialogo Dividir columna por delimitador -> seleccionamos el tipo de delimitador Personalizado, para este ejemplo, el delimitador es el signo menos ( – ) -> habilitamos la opción En cada aparición del delimitador -> Aceptar.
Cuarto: Usar la primera fila como encabezado
Pestaña Transformar -> grupo Tabla -> Comando Usar la primera fila como encabezado -> y damos clic en la opción del mismo nombre.
Quinto: reordenar las columnas, mover la columna fecha a la izquierda
Manteniendo presionada la etiqueta de la columna fecha con cliz izquierdo -> arrastramos la columna hasta la primera posición de la tabla ( de izquierda a derecha)
Sexto: Poner el tipo de dato apropiado a cada columna.
Clic derecho sobre la columna fecha -> Cambiar Tipo -> Fecha
Manteniendo CTRL presionado Clic izquierdo sobre la columna No de Transacción y # de Unidades Vendidas -> Cambiar Tipo -> Número Entero.
Séptimo: Quitar columna No. de Pedido
Clic derecho sobre la columna No de pedido -> Quitar
Nota: Los pasos como se ve en el panel, quedan registrados, por lo que podemos sistematizar este proceso y, para futuras transformaciones sólo tenemos quedar un único clic. En un artículo posterior hablaremos más a fondo sobre esto. ¡Don’t Panic!
Manteniendo presionada la etiqueta de la columna fecha con cliz izquierdo -> arrastramos la columna hasta la primera posición de la tabla ( de izquierda a derecha)
3. Cargar (Load)
Para finalizar, vamos a dar clic en Cerrar y Cargar. Y automáticamente se crea en una nueva hoja, una tabla estructurada con los datos listos para ser utilizados.
Archivo – Consolidado de Ventas
1. Importar Tabla Estructurada a Power Query (Extract)
Es necesario crear una consulta con la tabla estructurada Infoproductos:
Con la celda activa sobre la tabla -> Pestaña Power Query -> grupo Datos de Excel -> elegimos la opción Desde tabla.
Verificamos que la tabla tenga el tipo de dato apropiado en cada columna y que tenga una estructura tabular y en ese caso, podemos saltar al tercer paso Cargar.
3. Cargar (Load)
En este paso haremos una pequeña variación con respecto a los demás artículos, en lugar de cargar los datos en una tabla estructurada de Excel lo haremos como Conexión Solo Consulta.
Con la consulta lista para ser cargada nos dirigimos al comando cerrar y cargar -> en esta ocasión elegimos la opción Cerrar y Cargar en -> y en el cuadro de dialogo que aparece elegimos la opción Solo conexión.
Y como podemos observar en el panel de consultas del libro se crea la conexión sin tener que crear la tabla nuevamente y duplicar información.
Nota: Existen tres tipos de carga en Power Query, las tablas estructuradas, Conexión – Solo Consulta y la carga directa en el modelo de datos, cada una tiene sus beneficios y complicaciones, sin embargo, hoy no profundizaremos en este tema que sin duda es de gran importancia.
Para tener en cuenta:
Conexión – Solo Consulta
Este método de carga, evita la construcción de una tabla estructurada en una nueva hoja del libro, y es ideal para evitar duplicar los datos, al cargar la consulta de esta manera, nos aseguramos de crear una conexión a los datos, para poder trabajar con ellos en una solución directamente en Excel o con otras consultas.
Este procedimiento resulta ser muy útil, sin embargo, solo podemos visualizarlas fácilmente en el panel de consultas del libro y son muy poderosas para hacer nuestras soluciones más eficientes al evitar duplicar información, innecesariamente.
Y ahora algo de “magia”
Combinar en Power Query
Este comando es muy útil para relacionar tablas, procedimiento que es completamente diferente de Anexar, ya que en pocas palabras, el comando anexar une debajo de la consulta actual, otras consultas.
Por otro lado, el comando combinar relaciona o entreteje las tablas dada dos columnas, y por este método podemos crear relaciones entre tablas de tipo: muchos a muchos, en el vídeo 5 de la mini-seríe: Nociones en Power Pivot, se muestra este método como opción alterna a relación muchos a muchos en Power Pivot.
Para entretejer las consultas, vamos a ir a la pestaña Power Query y en el grupo combinar damos clic en el comando del mismo nombre.
Elementos a definir en el cuadro de dialogo Combinar:
- Definir Primer Tabla
En el cuadro de dialogo combinar -> elegimos en la primer tabla Consolidado de Ventas. Recordemos que en la primera tabla, vamos a agregar las demás columnas dada la “relación”.
- Definir Segunda Tabla
En la segunda tabla, seleccionamos la tabla de Infoproductos que es la que contiene las columnas faltantes y que son de nuestro interés, para el posterior análisis.
- Definir Columnas para Crear relación.
En ambas tablas dentro del cuadro de dialogo, damos clic izquierdo sobre las columnas con los códigos o Id de producto. Debemos estar seguros que en una tabla se encuentren los Id de producto una sola vez y en la otra tabla no importa si se encuentren repetidos o no, este tipo de relación se denomina Uno a muchos.
Pero bueno para finalizar damos clic en Aceptar.
Automáticamente, aparece el editor de consultas con una nueva consulta llamada Merge1.
En esta nueva consulta, se crea la tabla maestra o tabla relacionada.
Para mostrar las columnas faltantes, vamos dar clic en el botón de que aparece en la columna NewColumn, con dos flechas en sentido contrario.
En la lista que aparece, debemos elegir que columnas de la tabla Infoproductos, queremos agregar a esta tabla, para esta caso solo quitamos la columna Id de producto y adicionalmente, deshabilitamos la opción usar el nombre de la columna original como prefijo y damos clic en Aceptar.
Y mira lo que pasa a continuación:
Se han agregado las otras columnas para crear una tabla maestra algo sencillamente espectacular y con tan poco esfuerzo. Para finalizar, vamos a modificar el nombre de las columnas Id de producto y Nombre de producto, así como el nombre de la consulta y damos clic en cerrar y cargar.
Y Bangggg¡¡
Se crea en una nueva hoja nuestra tabla maestra.
Para finalizar, vamos a crear una tabla dinámica y un gráfico dinámico en una nueva hoja, ubicando en el área de filas, los campos producto y categoría, en el área de valores el campo # de Unidades Vendidas y en el área de filas el campo Fecha y este es el resultado que obtenemos.
Un reporte con toda la información de las unidades vendidas en el primer día de febrero, listo para ser utilizado.
Nota: Hay un aspecto que quiero señar aquí, aunque el artículo enumero el proceso ETL dos veces, en realidad es un único proceso, pues se importan (Extract) las n-tablas a Power Query y se tranforman para después cargarlas. En el artículo lo enumeramos como si fueran dos veces, por la dinámica del mismo.
Nota: Si quisieras agregar la información del segundo día al reporte, podrías crear una nueva consulta con dicha información, transformar y limpiar los datos, y luego ANEXAR la consulta del segundo día a la del primero y posteriormente actualizar todo el reporte y de esta manera no tendrías que repetir todo el proceso para cada día, pero bueno, más adelante veremos diferentes procedimientos para actualizar información fácilmente con Power Query.
Bueno mi amig@
Por el día de hoy ha sido todo, espero que este articulo te resulte de mucha utilidad.
Hasta la próxima