Unir hojas de un archivo Excel con Power Query

Casi todo usuario de Excel se ha enfrentado en algún momento a la tarea de unir datos de distintas hojas de un cuaderno. Por ejemplo, en un cuaderno Excel tenemos cuatro hojas conteniendo cada una los datos de ventas de cada uno de los trimestres del año. De la misma manera podríamos tener un cuaderno con 12 hojas, una por cada mes; o 48 hojas, una por cada mes de los últimos dos años; o…bien, la idea queda clara.

Si se trata de unir dos o tres tablas, que tienen la misma estructura, un simple Copiar y Pegar nos resuelve el problema. Pero cuando tenemos un número considerable de hojas Copiar y Pegar no sólo es tortuoso sino que puede conducir a omisiones o errores.

Power Query nos permite hacerlo con unos pocos clics y sin necesidad de abrir el archivo, de manera que podemos crear un nuevo archivo sin alterar el original.

Supongamos que nuestro original contiene cuatro hojas con los datos de ventas de cada uno de los trimestres del año. En la pestaña del Power Query (en Excel 2010/13, Datos Obtener y Transformar en 2016) creamos una consulta 

En la ventana del Navegador podemos ver las cuatro hojas del cuaderno. En lugar de seleccionar las hojas una por una, hacemos un clic al nombre del archivo (Ventas anuales por trimestre.xlsx) y apretamos el botón Edit. El resultado es el siguiente

Filtramos la tabla dejando visible sólo los valores “Sheet” en la columna “Kind”

con este resultado

Ahora eliminamos todas las columnas excepto “Data” (opcionalmente podemos dejar también la columna “Name” para identificar el origen de los datos) y apretamos el botón Expand (la doble flecha a la derecha del encabezado de la columna)

No nos dejamos inquietar por la advertencia “List may be incomplete” y apretamos “OK” con decisión. 

Como podemos apreciar, los encabezamientos aparecen en la primer fila por lo que deberemos promoverlos con “Use first row as headers”. Ahora se nos presenta un último problema: cada tabla en cada hoja cuenta con encabezamientos por lo que éstos aparecen en las filas de la tabla unificada y debemos eliminarlos.

Para hacerlo usamos el filtro en alguna de las columnas, por ejemplo en la primera quitamos la marca de "Customers.CompanyName"

Todo lo que nos queda por hacer es apretar Close and Load y ya tenemos nuestros datos integrados en una única tabla.

Consolidar archivos con Power Query - el caso ".tsv"

En la nota anterior mostramos como consolidar datos de varios archivos .csv (comma separated values) con Power Query. Estos son archivos de texto donde los campos están separados por comas o puntos y comas. Existen también archivos de texto .tsv donde, como en los archivos .csv, cada registro de la tabla es una línea del archivo de texto y cada campo de un registro se separa del siguiente por un carácter de tabulación - que es una forma del formato más general valores delimitador separados.

TSV es un formato de archivo simple ampliamente difundido que a menudo se utiliza para mover datos tabulares entre los diferentes programas informáticos que soportan este formato. Por ejemplo, un archivo de TSV puede ser utilizado para transferir información de un programa de base de datos para una hoja de cálculo. Y este es el caso de  un colega que después de haber leído mi post, vino a consultarme cómo hacer lo mismo con archivos .tsv.

La cuestión es que Power Query no cuenta con una función Tsv.Document para extraer datos de este tipo de archivos y la función Csv.Document, que mostramos en el post mencionado, si bien cuenta con un parámetro para indicar el separador a usar ([Delimiter]), pero todos mis intentos de indicar la tabulación como separador (#"tab", "#tab", #(Tab), etc.) terminaron en rotundos fracasos.

La solución fue importar los archivos al editor del Power Query, tal como hicimos con los archivos csv, agregar una única columna con los datos y luego separar esta columna con la funcionalidad Split Column del menú de Power Query.

Veamos el proceso. Después de seleccionar la carpeta que contiene los archivos a consolidar y abrirla en la ventana del Power Query (ver el proceso en el post anterior), agregamos la columa "Datos" creada con la fórmula =Csv.Document([Content])

Esta fórmula crea una columna que expandimos apretando la doble flecha en el encabezado

con este resultado

Eliminamos las columnas Content y Name con lo que nos queda una única columna con todos los datos. La funcionalidad Split Column by Delimiter si tiene el valor Tab como separador

con este resultado

Todo lo que nos queda por hacer es promover la primer fila a los encabezados

y transferir la tabla a una hoja de Excel o crear una conexión a la consulta

Consolidar datos de archivos .csv con Power Query

Este post es la continuación del anterior sobre la consolidación de datos con Power Query.
La misma técnica puede aplicarse si los archivos a integrar están en formato .csv (texto, comma separated values).
La "magia" del proceso son las funciones de extracción de datos del Power Query (el lenguaje "M"). Una reseña sucinta sobre las funciones del Power Query puede verse en esta página. Una lista detallada de toda las funciones "M" (el lenguaje del Power Query) puede obtenerse aquí.

En este post vamos a reveer los pasos para integrar datos de cuadernos guardados en una misma carpeta. Como en el caso anterior, partimos de la base que las tablas de datos en los distintos cuadernos tienen la misma estructura.

Como en el caso de los archivos .xlsx, usamos la opción From Files - From Folders del Power Query

Como en el caso anterior, recibimos una tabla con una fila por cada archivo de la carpeta seleccionada

Eliminamos todas las columnas excepto Content y Name y hacemos el "pase mágico": creamos una columna personalizada con la fórmula =Csv.Document([Content]), y recordemos que a diferencia de las funciones de Excel, debemos respetar las mayúsculas

La fórmula creará una nueva columna (eso es lo que las funciones M hacen)

que procedermos a expandir apretando al doble flecha en el ángulo derecho del encabezamiento de la columna

con este resultado

Lo último que nos queda por hacer es utilizar la primer fila de la tabla como encabezamiento de las columnas

Podemos eliminar la columna "Column1" (Binary) que ya no cumple ninguna función. La segunda columna contienen el nombre del archivo de donde se extrajo la fila. También esta columna puede eliminarse y si queremos dejarla para identificar el origen de los datos, debemos cambiar el nombre.

Finalmente, pasamos la tabla a una hoja del cuaderno Excel o creamos una conexión a la consulta.

Este video muestra todo el proceso

Integrar datos de archivos cerrados con Power Query

Consolidar datos de distintos archivos es una tarea frecuente para todo analista que use Excel. Tal es así que este blog ha tratado el tema desde distintos ángulos: usando MS Query, Access, SQL, Vba (macros) y últimamente con Power Query.

La técnica que mostré en el post anterior puede parecer complicada e incluso intimidante para quien recién se inicia en el uso del Power Query (nueva interfaz, nuevo idioma). Para nuestra fortuna, Miguel Escobar publicó en el pasado una técnica sencilla, que pueden ver en esta nota (y continuada luego en esta otra nota), y que reproduzco aquí en una versión simplificada (parece ser que Miguel eliminó la versión en castellano del post).

Power Query nos permite consolidar datos de distintos archivos sin necesidad de abrirlos, sin tener que programar Vba, sin SQL, en pocos pasos y con facilidad.

En nuestro ejemplo deben cumplirse dos condiciones:

1 - los archivos deben tener la misma estructura (número y tipo de columnas);
2 - los archivos se encuentran en la misma carpeta.

Supongamos tres archivos con las ventas de una empesa imaginaria

Nuestra misión en consolidar los datos de los tres años en una única tabla que luego podremos analizar con tablas dinámicas, mejor aún, con PowerPivot.

Empezamos toda la carpeta a la ventana del Power Query

obteniendo este resultado en la ventana del editor del Power Query

Además de los tres de ventas, la carpeta contiene otros archivos así que filtramos la tabla dejando visibles sólo los archivos con la extensión ".xlsx"

Eliminamos todos los campos en la tabla excepto "Content" (Binary ) y "Name". Una de las tantas cosas buenas del Power Query es que podemos seleccionar las columnas que queremos dejar y elegir la opción Remove Other Columns (eliminar las otras columnas)

muy práctico cuando queremos eliminar la mayor parte de las columnas en el ventana.

Ahora viene el paso crítico en el proceso: vamos a crear una nueva columna, "Datos", definida con la fórmula =Excel.Workbook([Content],true)

¿Qué es esta expresión "=Excel.Workbook([Content],true)"? Power Query tiene su propio lenguaje de funciones (conocido como "M"). La función Excel.Workbook pertenece a la categoría de funciones que permiten acceder a distitnas plataformas de datos y extraerlos en forma de tabla. El valor "true" delsegundo argumento de la función hace que la primer fila de la tabla de datos de la hoja sea usada como encabezamiento de las columnas.

Al escribir la fórmula debemos tomar en cuenta que, a diferencia de las de Excel, en las funciones del lenguaje del Power Query las mayúsculas y minúsculas tienen importancia. Si escribimos =excel.workbook, resultará un error. Siguiendo con nuestro ejemplo, introducimos la fórmula y apretamos OK para crear la nueva columna. Este es el resultado

Ahora expandimos la columna Datos apretando la doble flecha en el lado derecho del encabezado de la columna

Power Query nos ofrece abrir todas las columnas de "Table" pero dejamos sólo la marca en "Data" y apretamos OK

Ahora expandimos la columna "Data"

En este caso vamos a dejar todas las columnas

Los próximos dos pasos son:

# - eliminar las columnas "Content" y  "Name" que, en nuestro caso, no cumplen ya ninguna función;
# - cambiar el tipo de dato de la columna Fecha de "any" (general) a "Date" (fecha)

El última paso es pasar (load) la tabla que hemos creado a una hoja de Excel o crear sólo una conexión

Si elegimos la primer opción (Close and Load), Excel exporta la tabla a una hoja del cuaderno

Tablas dinámicas - uso de Rangos de Consolidación Múltiples

A partir de la versión 2007 de Excel, la funcionalidad "Rangos de consolidación múltiples" desapareció del asistente de de tablas dinámicas:

Pero el "viejo" asistente no desapareció; se puede acceder a él agregando el icono a la barra de herramientas de acceso rápido

En esta nota voy a mostrar una solución posible a la consulta de un lector y al pasar algunas consideraciones sobre cuál es la mejor manera de organziar datos en Excel.

Empecemos por presentar el problema. Nuestro lector tiene esta matriz de datos

pero para poder consolidar datos y realizar cálculos, necesita organizar los datos de esta manera (más adelante veremos si realmente ésta es la mejor manera)

Si nuestra matriz contiene muchos datos la posibilidad de cortar y pegar o arrastrar los rangos puede ser poco práctica. En ese caso podemos echar mano a la funcionalidad Rangos de consolidación múltiples del viejo asistente de tablas dinámicas.

El primer paso es abrir el asistente y seleccionar los rangos a consolidar

Al apretar el botón "Finalizar" obtenemos la tabla

Ahora podemos seleccionar la tabla dinámica y usar Copiar-Pegar Valores para poder usarla como una matriz de datos regular.

Este video muestra el proceso


Los datos quedan organizados como un matriz de datos "plana". En lugar de la forma "plana" de la tabla dinámica, donde cada "Tag" es una columna, podemos organizar los datos en forma tabular con una única columna para los "Tags". Esto reduce el número de columnas de la tabla y hace que sea más fácil de manejar para nuestros cálculos.
Para convertir la tabla dinámica de rangos consolidados en una matriz de datos tabular hacemos un doble clic en el total general

Como resultado se abre una nueva hoja con la matriz de datos

Ahora podemos cambiar el encabezado de las columnas A, B y C y eliminar la columna D.

Totalizar datos en Excel con Datos-Consolidar

Uno de los métodos con los que cuenta Excel para consolidar datos de distintas hojas o cuadernos es Datos-Consolidar (pueden ver esta prehistórica nota en el blog o apretar el enlace Consolidar Datos en la nube de etiquetas).

También podemos usar este método para consolidar rápidamente los datos de una tabla. Supongamos que tenemos una tabla de 1000 filas que detalla las ventas del mes de noviembre. Dado que cada venta se anota por separado, cada fecha del mes aparece varias veces. Nuestro objetivo es totalizar las ventas por día (si, por supuesto que podemos hacerlo con una tabla dinámica; pero aquí mostraremos como hacerlo con Datos-Consolidar),

Supongamos que las ventas están en el rango A1:B1001; el primer paso será definir un nombre que se refiera a este rango. Seleccionamos el rango y introducimos el nombre en la cuadro de nombres

El próximo paso es seleccionar una celda, en la misma hoja o en la hoja donde queremos que aparezcan los datos totalizados, y activar el menú Datos-Consolidar

Nos aseguramos que la función sea "Suma", en la referencia pegamos el nombre que se refiere al rango de los datos (podemos usar F3 para pegar el nombre) y marcamos las opciones "Fila superior" y "Columna izquierda" en la opción "Usar rótulos". Finalmente apretamos Aceptar

Excel crea instantáneamente una tabla totalizando las ventas por fechas.

Detalles a tener en cuenta:

• Excel no pone el encabezamiento en la primer columna (la celda D2 en nuestro ejemplo), por lo que debemos agregarla manualmente;
• en la tabla de totales las fechas aparecerán con formato General, por lo que debemos aseugrarnos de pre-formar el rango de las celdas o hacerlo después de crear la tabla.

Consolidar datos de varios cuadernos Excel con MS Query

Ya hemos tratado anteriormente el tema de consolidación de datos de varios cuadernos u hojas Excel usando MS Query. Sin embargo el uso de esta herramienta presenta ciertos problemas y la ayuda en línea del MS Query es muy pobre.

En esta nota veremos cómo lograr esta consolidación para lo cual tendremos que editar y modificar la consulta SQL que genera el MS Query. Aclaremos que esta tarea es sencilla y no requiere conocimientos del lenguaje SQL.

En nuestro ejemplo suponemos que tenemos los datos de ventas de tres sucursales de una empresa en tres cuadernos distintos: ventasNorte.xls, ventasSur.xls y ventasOeste.xls. Las tablas de datos de cada cuaderno tienen la misma estructura, es decir, los mismos campos (columnas) en las mismas posiciones.

Empezamos por incluir cada tabla en un nombre. Por ejemplo, en ventasNorte.xls incluimos la tabla de datos en el nombre consNorte

También podemos crear el nombre usando el cuadro de nombres

El próximo paso es abrir un cuaderno en blanco donde consolidaremos los datos. En el nuevo cuaderno activamos la pestaña Datos y en Obtener Datos Externos usamos la opción Desde Microsoft Query

En Excel 2003 usamos

En Elegir origen de datos seleccionamos Excel Files

En el paso Select Workbook (seleccione cuaderno), seleccionamos uno de los cuaderno, por ejemplo, ventasNorte.xls

En el próximo paso seleccionamos el nombre del rango y pasamos todas las columnas a la consulta

Seguimos adelante en el proceso hasta llegar al último paso donde seleccionamos la opción Ver datos o modificar en Ms Query

Lo que veremos en la interfaz del MS Query que se abre es esto

Apretamos el botón SQL, lo que nos permite acceder a la sintaxis de la consulta.

En lugar de

SELECT rngNorte.Sucursal, rngNorte.Mes, rngNorte.Ventas, rngNorte.Clientes
FROM `D:\Ventas\ventasNorte`.rngNorte rngNorte

ponemos

SELECT * FROM `D:\Ventas\ventasNorte`.rngNorte
UNION ALL
SELECT * FROM `D:\Ventas\ventasSur`.rngSur
UNION ALL
SELECT * FROM `D:\Ventas\ventasOeste`.rngOeste

Al apretar Aceptar veremos

Apretamos Aceptar y todos los datos serán consolidados en la tabla del MS Query

En el menú del Query elegimos Archivo-Devolver los datos a Excel lo que abre el diálogo de Importar datos en Excel, donde podemos elegir la forma de ver los datos en Excel. Por lo general usaremos la opción Informe de tabla dinámica

En Excel 2003 la interfaz es algo distinta