Saludos amig@ de EFB
Recuerdas el artículo: Combinar archivos con Power Query, el segundo de la serie de publicaciones dedicadas a explorar las bondades de Obtener y Transformar como se le conoce a esta herramienta en Excel 2016, pues bien, en esa ocasión, cabe resaltar que además de utilizar la versión 2013, también, nos enfocamos en entender al máximo la funcionalidad de Anexar (Pegar varias tablas, una debajo de otra a través de una consulta). y NO en crear la solución más apropiada para trabajar con varios archivos.
Por esa razón, hoy vamos a resolver el mismo problema, pero con un mejor método, así que manos a la obra
– Ejemplo – Ingresos Múltiples Tiendas
Como en aquella ocasión: Nuestro objetivo, es crear un reporte con diferentes perspectivas sobre los ingresos obtenidos en diferentes tiendas de una misma cadena de supermercados, a continuación, puedes ver un vistazo de cómo se componen los archivos suministrados por cada una de las sedes, con datos relacionados a los ingresos obtenidos día a día, desde el mes de enero, hasta marzo de 2016
Para resumir el artículo, Combinar archivos con Power Query , creamos una consulta que permite “pegar” las tablas de cada tienda, una debajo de otra, a través de la funcionalidad de Anexar en Power Query, pero como ya lo mencionamos, no es el método más útil para trabajar con archivos con archivos de texto o csv.
En ese orden ideas, la mejor forma para trabajar con varios archivos de las mismas características desde Power Query es importar los datos desde una carpeta …
Recordemos que el especialista en la primera etapa de la creación de una solución en inteligencia de negocios, es Power query, y extrayendo los datos de esta forma, podremos facilitar el proceso de actualización, veamos por que.
– Solución
1. Extraer (Extract)
Primero debemos “extraer” o conectar los datos, eligiendo la opción Desde una carpeta, a continuación, puedes ver todo el procedimiento en detalle:
Luego, en el cuadro de dialogo que aparece, elegimos la ruta de la carpeta con los archivos a unificar en una sola tabla.
Damos clic en el comando Examinar y elegimos la ubicación en nuestro computador de la carpeta -> damos clic en Aceptar.
Como ya tenemos la ruta de acceso a la carpeta, damos clic en Aceptar
Después, aparece un panel con la vista previa de la tabla cargada con la información de los archivos, y por lo visto debemos editarla…
Damos clic en Editar y a continuación aparece el Editor de consultas – ¡El lugar donde la magia ocurre¡
Con una tabla que muestra información detallada de cada archivo y NO, su contenido, en consecuencia debemos editar el resultado obtenido a través de la consulta actual, agregando más pasos.
2. Transformar (Transform)
Ahora bien, la tabla que muestra el Panel de Resultados, contiene la información relacionada a cada archivo, para mostrar el contenido, debemos realizar el siguiente procedimiento:
Mostrar contenido
Presta mucha atención a la siguiente serie de pasos, ya que fácilmente podemos importar y transformar archivos binarios que tengan la misma estructura.
Eliminar Columnas
Antes de mostrar las tablas contenidas en los diferentes archivos, debemos eliminar las columnas que no son necesarias
Seleccionar múltiples Columnas
Damos clic izquierdo sobre la primera columna a eliminar, para seleccionarla.
Luego, manteniendo Shift presionado, vamos a dar clic sobre la última columna de la tabla para seleccionar todas las columnas entre la primera y la última, incluyéndolas a ambas.
Eliminar Columnas
Damos clic derecho sobre la etiqueta de alguna columna seleccionada -> y en el menú de opciones que se despliega, elegimos Quitar Columnas
Obteniendo así, la siguiente tabla:
Botón Combinar
Para mostrar y unificar las tablas de los tres archivos de texto, vamos a dar clic sobre el botón Combinar que tiene como símbolo, dos flechas y una línea horizontal, y que aparece en la etiqueta de la columna “Content”
Al dar clic sobre él, veamos que pasa
Se muestra el contenido de cada uno de los archivos, y como su estructura es la misma, automáticamente “anexa” una tabla debajo de la otra.
Para verificarlo, podemos desplazarnos en el Panel de Resultados, aunque este no es un método muy confiable si trabajamos con más de mil filas (que es casi la mayoría de veces), porque el panel de resultados solo muestra una porción de los datos, si el número de registros es mayor a mil.
En la imagen anterior, podemos verificar que se han anexado una tabla debajo de otra, aunque, podemos ver que no se quitaron las etiquetas de las otras tablas
Nota: Una forma de verificar rápidamente que contiene cada columna, es dando clic en el botón Opciones de filtro que aparece en la etiqueta de cada columna (un botón que tiene como símbolo, una flecha).
Clic sobre el botón de filtro de la columna Categoría de Producto
En las opciones de filtro inhabilitamos la opción categoría de producto -> damos clic en Aceptar
Y así de simple, hemos quitado filas de nuestra “tabla”, dependiendo de un ítem en particular
Nota: Los filtros son una forma muy eficaz de eliminar filas de una tabla obtenida a través de una consulta.
Recuerda que, si no conoces muy bien el origen de datos, puedes utilizar las opciones de filtro para ver rápidamente que tipos de ítem contiene la columna en cuestión.
No olvidemos que la última columna esta vacía, por lo que debemos eliminarla
Clic derecho sobre la columna -> Quitar
Para finalizar la etapa de transformación, verificamos que el tipo de dato de cada columna, sea el apropiado y reescribimos el nombre de la consulta como Tabla_Maestra
3. Cargar (Load)
Para finalizar, el proceso ETL, vamos a cargar la tabla en el modelo de datos.
Para ello damos clic en Cerrar y Cargar en –> y en el cuadro de dialogo que aparece habilitamos las opciones, Crear solo Conexión y Cargar estos datos en el Modelo de Datos -> Cargar
En el panel de consultas, podemos ver que se han cargado exitosamente los datos, en el modelo de datos de Excel.
Con los datos transformados y cargados en el modelo de datos, es hora de enfocarnos en analizarlos
Modelación
Crear Tabla Dinámica
Clic en la pestaña Insertar -> grupo Tablas -> clic en el botón Tabla Dinámica
En el cuadro de dialogo Crear Tabla Dinámica habilitamos las opciones -> Usar el modelo de datos de este libro -> Hoja de Cálculo Existente -> y damos clic en Aceptar.
Cerramos el panel de consultas del libro porque en este momento no lo necesitamos, dando clic en la X y ahora solo resta, empezar a crear nuestro reporte de tabla dinámica.
Arrastramos el campo fecha al área de columnas, el campo ingreso al área de valores y el campo Sede al área de filas.
Nota: recuerda que, por defecto, al ser un campo con datos de tipo fecha, en la versión de Excel 2016, la tabla dinámica automáticamente agrupa el campo.
Para finalizar Personalizamos la Tabla dinámica que recién hemos creado, modificando las etiquetas y eligiendo el estilo de tabla dinámica claro 1
Visualización
Rápidamente, vamos a agregar y editar un gráfico dinámico a partir de la tabla dinámica que recién hemos creado y un Slicer Con las Categorías de Producto.
Nota: Recuerda asignar un nombre descriptivo a cada hoja de tu reporte
El slicer y el gráfico dinámico han sido creados a partir de la tabla dinámica, por lo que todos, trabajan en equipo, por ejemplo, seleccionemos la categoría, Alimentos
Automáticamente se actualiza el reporte.
Si en el gráfico dinámico filtramos dos meses, esto es lo que sucede:
Actualización del Reporte
Veamos un segundo el origen de datos, o la carpeta donde se encuentran los archivos:
Qué pasa si agregamos a la carpeta, un nuevo archivo con los datos de la Tienda D.
Vayamos un segundo, al libro donde creamos el reporte de ingresos.
Y en el panel de consultas del libro, vamos a dar clic sobre el botón Actualizar que aparece, cerca al nombre de la consulta.
Pues bien, el reporte se ha actualizado eficientemente, sin necesidad de tener que limpiar el ultimo archivo que recién hemos agregado, cabe resaltar que debido a que aumentan los elementos de la tabla dinámica y el gráfico, debemos reajustarlos rápidamente.
Y así, con Power Query podemos crear soluciones que se actualicen fácilmente.
Nota: En el panel de consultas, podemos ver como se han agregado los nuevos datos a nuestra tabla cargada en el modelo de datos.
Bueno espero que este articulo sea de mucha ayuda y que puedas ahorrar tiempo implementando Power Query en la creación de tus soluciones en Excel o Power BI, en las próximas publicaciones seguiremos hablando de otras aplicaciones que tiene Importar datos Desde una Carpeta.
Hasta la próxima