Unir varios archivos y hacer Tabla dinámica de más de 1 millón de registros usando Power Query y Power Pivot en Excel
Para este ejemplo tenemos 5 archivos en formato .CSV y cada uno contiene 500 mil registros, haciendo un total de 2 millones 500 mil registros, los cuales es imposible manipularlos en una hoja de Excel. Cada hoja de Excel, como sabemos, el número de filas es de 1’048,576 filas. Poco más de un millón de registros por hoja.
Power Query y Power Pivot en Excel 2016
Una de las novedades de Excel 2016 es que los complementos Power Query y Power Pivot ya vienen incluídas en las versiones Pro, aunque si deseamos usarlas en una versión que no sea Pro siempre las podremos descargar. A continuación las definiciones de ambos complementos:
Qué es Power Query?
Es un complemento de Excel que permite al usuario de negocio conectarse a cualquier origen de datos; transformar, combinar y acomodar los datos según la necesidad, y cargarlos a una hoja Excel o, mejor aún, a un modelo de datos (Power Pivot). Además, todo el proceso de conexión, transformación, combinado y acomodo de los datos queda grabado para poder ser reutilizado posteriormente (cuando, por ejemplo, los datos se refresquen o haya que incluir los del siguiente periodo). Es posible también buscar y conectarse a fuentes públicas de datos. Descargar Power Query.
Qué es Power Pivot ?
PowerPivot es un complemento para Microsoft Excel que le permite importar millones de filas de datos de varios orígenes de datos en un único libro de Excel, crear relaciones entre datos heterogéneos, crear columnas calculadas y medidas mediante fórmulas, generar tablas dinámicas y gráficos dinámicos, y analizar aún más los datos para que pueda tomar decisiones empresariales oportunas sin que sea necesaria la ayuda de TI. Descargar Power Pivot.
Unir varios archivos usando Power Query
Como lo comentaba al inicio, tenemos 5 archivos con 500 mil registros cada uno, los cuales deseamos unir para hacer una Tabla dinámica. Para esto, haremos uso de dos herramientas de Bussines Intelligence que proporciona Microsoft para el tratamiento de datos: Power Query y Pover Pivot.
Primero recomiendo tener los archivos que vamos a unir, en una misma carpeta, ya que le vamos a indicar a Power Query que tome los archivos de dicha carpeta.
Figura 1. Se recomienda tener los archivos a unir en una misma carpeta.
En Excel 2016 vamos a irnos a la pestaña Datos. Dentro de la pestaña elegimos la opción Nueva Consulta > Desde un archivo > Desde una carpeta.
Nota: En Excel 2010 elegimos la pestaña Power Query.
Figura 2. Obtener datos de una carpeta con Power Query.
Elegimos la carpeta que contiene los 5 archivos. Enseguida se mostrará un formulario donde tendremos una tabla con 5 filas. Cada filas corresponderá a la descripción de cada archivo. Damos clic en la columna llamada Content y se mostrarán todos los registros de todos los archivos en una misma tabla.
Figura 3. Unir más de un archivo con Power Query.
El formulario mostrará los registros de los 5 archivos. Ahora lo que tenemos que hacer es cargar los datos al “Modelo de datos” para usarlo en Power Pivot. Elegimos la opción Cerrar y cargar > Cerrar y cargar en…. Ahora elegiremos la opción Crear solo conexíón y marcamos la opción Agregar estos datos al Modelo de datos.
Figura 4. Cargar los datos al Modelo de datos.
Hacer Tabla Dinámica con Power Pivot
Una vez que se cargaron los datos al Modelo de datos, en la parte derecha de Excel veremos un panel con el número registros que están cargados.
Figura 5. Aquí vemos el número de registros cargados al Modelo de datos.
Para hacer la Tabla Dinámica nos vamos de nuevo a la pestaña y damos clic en Administrar modelo de datos. Si no tenemos habilitado el complemento de Power Pivot veremos un mensaje que nos dice “Habilite los complementos de análisis de datos para usar esta característica”.
Al dar clic en Habilitar se nos mostrará la ventana Power Pivot donde veremos los datos del los 5 archivos que previamente cargamos. En dicha ventana damos clic en Tabla dinámica y elegimos el destino donde queremos que se inserte.
Figura 6. Insertar Tabla Dinámica con Power Pivot.
Por último armaremos nuestra Tabla Dinámica según nuestras necesidades.
Figura 7. Armar nuestra Tabla Dinámica en Excel.
