Cómo usar Power Query y Power Pivot en Excel como un profesional

Si quieres ser un verdadero usuario avanzado de hojas de cálculo, necesitas aprender sobre las funciones de Power Query y Power Pivot en Excel. Aunque puedes hacer mucho solo con Excel, usar estas funciones “Power” integradas te convertirá en un usuario profesional avanzado de Excel. En esta guía, aprenderás cómo usar Power Query para importar potencialmente cientos de archivos diferentes con millones de filas y usar Power Pivot para generar análisis complejos sobre los enormes modelos de datos que importas.

Tabla de Contenidos

• 1. Importando Datos de Múltiples Archivos
• 2. Filtrando los Archivos y Filas Importados
• 3. Cargando Datos Filtrados en un Modelo de Datos
• 4. Analizando Datos con PivotTable
• 5. Presentando tus Datos con PowerChart
• Preguntas Frecuentes

¿Quieres proteger tu trabajo de miradas curiosas? Aprende cómo agregar una contraseña a tu libro de Excel.

1. Importando Datos de Múltiples Archivos

Usando Power Query, puedes importar datos de diferentes archivos en una sola carpeta. Ten cuidado, sin embargo, ya que aún debes asegurarte de que todos los datos sigan el mismo formato. Mantén el número de columnas y los nombres de los encabezados de columna iguales y asegúrate de que el tipo de datos para los valores en cada columna sea consistente.

Para esta guía, estamos usando cinco archivos que comprenden ~100 millones de filas de datos de Kaggle. Descárgalos si quieres seguir el ejemplo o usa tus propios datos. Con eso en mente, aquí están los pasos para importar los datos:

1. Haz clic en la pestaña “Datos” en la cinta.
2. Selecciona “Obtener Datos -> Desde Archivo -> Desde Carpeta,” luego navega hasta tu carpeta y selecciónala.

1. Deberías ver una nueva ventana emergente. En nuestro caso, tenemos cinco archivos de prueba que están nombrados según su tamaño: “adult10m” tiene 10 millones de filas de datos, “adult1m” tiene 1 millón de filas, “adult100k” tiene 100,000 filas, y así sucesivamente. Haz clic en “Transformar Datos” para abrir el Editor de Power Query.

2. Filtrando los Archivos y Filas Importados

En el Editor de Power Query, puedes aplicar filtros para importar solo los archivos que coincidan con los criterios que establezcas, lo cual es muy útil para eliminar fuentes de datos que no deseas.

1. Tenemos cuatro archivos sin extensión de archivo, pero un archivo con la extensión .DATA. Vamos a eliminar el extraño haciendo clic en la flecha junto a “Extensión.”

1. Podemos ver una lista de todas las extensiones de archivo de nuestros archivos. En este ejemplo, estas son solo (vacío) y .data. Desmarca las extensiones que no deseas.

1. Cada filtro que aplicamos aparecerá en la sección “Pasos aplicados”.
2. También podemos filtrar por nombre de archivo haciendo clic en la flecha junto a “Nombre.”

1. Supongamos que queremos filtrar cualquier archivo que contenga “k” en ellos, como “adult100k”, porque no queremos trabajar con archivos pequeños que contengan menos de un millón de filas. Podemos aplicar un filtro de texto. Hay muchas opciones, pero para nuestros propósitos, el filtro “No contiene” es el correcto.

1. Escribe el texto que no quieres ver en el nombre del archivo.

1. Desplázate un poco hacia la derecha hasta que puedas ver las columnas “Fecha modificada” y “Fecha creada”. Vamos a filtrar para seleccionar solo archivos creados en un cierto rango de tiempo. Haz clic en la flecha junto a “Fecha creada.”

1. Elige “Filtros de Fecha/Hora -> Entre.”

1. Escribe los valores de tiempo para las dos condiciones y confirma presionando “OK.”

1. Hemos completado el filtrado de nuestros datos a nivel de archivo por archivo.
2. Vamos a combinar nuestros archivos restantes para que podamos filtrar los datos en sí. Haz clic en las flechas dobles junto al encabezado “Contenido.”

1. Ahora que puedes ver todos los valores para filas individuales, cada fila tiene una columna adicional, “Source.Name,” que indica el archivo de donde provino.

1. Para mejorar aún más nuestros datos: dado que nuestros archivos CSV originalmente vinieron sin nombres de encabezados de columna, puedes hacer clic derecho en algunos de los encabezados de columna y hacer clic en “Renombrar” para darles un nombre apropiado.

1. Los filtros también funcionan aquí. Podemos aplicar un filtro de rango numérico. Por ejemplo, haz clic en la flecha junto al encabezado de columna “Edad”, luego elige “Filtros de Números -> Mayor que.”

1. Ingresa un número como 30, luego haz clic en “OK.” Todas las filas con edades por debajo del número especificado serán excluidas.

Consejo: al trabajar con hojas de cálculo en Microsoft Excel, puede ser útil saber cómo mover una columna.

3. Cargando Datos Filtrados en un Modelo de Datos

Puedes seguir limpiando y filtrando los datos, pero pasemos a cargarlo en un modelo de datos para comenzar a analizarlo.

1. Termina con Power Query haciendo clic en “Cerrar y Cargar -> Cerrar y Cargar a.”
2. En la ventana emergente que se abre, selecciona “Solo Crear Conexión” y marca “Agregar estos datos al Modelo de Datos.”

1. Espera a que se carguen los datos. Esto puede tardar unos minutos si tienes una gran cantidad de datos.

Usando el Modelo de Datos de Power Pivot en Excel

1. Después de que se complete la carga de datos, podemos hacer cambios (como agregar nuevas columnas) al modelo de datos haciendo clic en “Datos -> Herramientas de Datos -> Administrar Modelo de Datos.”

1. Deberías ver la ventana de Power Pivot. Vamos a agregar una columna con una fórmula de Expresiones de Análisis de Datos (DAX). Las fórmulas DAX son muy similares a las fórmulas de Excel con las que probablemente ya estás familiarizado. Desplázate hacia la derecha hasta que veas la última columna de tus datos, luego haz clic en “Agregar Columna.”
2. Vamos a escribir una fórmula para esta columna que aplique un cálculo simple a cada fila. En comparación con las fórmulas de Excel, la sintaxis de DAX es ligeramente diferente. Por ejemplo, podemos agregar una columna llamada “Descripción” e ingresar la fórmula =CONCATENATE([Column9],[Column10]). Nota el uso de corchetes ([ ]) y el acceso a través de los nombres de los encabezados de columna.

1. Si lo deseas, también puedes realizar modificaciones adicionales como renombrar o eliminar columnas haciendo clic derecho en el encabezado de la columna.

4. Analizando Datos con PivotTable

Después de que hayas terminado de hacer cambios en el modelo de datos, es hora de analizar los datos en una hoja de Excel usando PivotTable.

1. Haz clic en “Inicio -> PivotTable” en la cinta de la ventana de Power Pivot para abrir la ventana “Crear PivotTable.”

1. Selecciona “Nueva Hoja” y presiona “OK.”

1. El panel de Campos de PivotTable se abrirá en una nueva hoja en la ventana principal de Excel. En el cuadro debajo de la barra de búsqueda, deberías ver tus fuentes de datos listadas, “kaggle_adult_csv” en este ejemplo. Haz clic en ella para expandirla y mostrar todas sus columnas.

1. Vamos a hacer un análisis donde contamos el número de cada “Descripción” en nuestros datos. Podemos seleccionar las columnas con las que queremos trabajar haciendo clic en la casilla de verificación junto a sus nombres. Selecciona “Descripción” y nota cómo el enfoque se desplaza automáticamente al área de “Filas” debajo. Esto es Power Pivot demostrando su inteligencia: puede adivinar con precisión el área apropiada para las selecciones. Dado que estamos comenzando desde una PivotTable en blanco, tiene sentido que nuestra primera selección sea filas.

1. Arrastra la columna “Descripción” marcada al espacio vacío en “Valores.” Nota que Power Pivot nuevamente asume correctamente que queremos un “Conteo de Descripción” debido a que el tipo de datos es texto. Además, nuestra tabla se ha actualizado con una columna que proporciona los conteos de cada ocurrencia de cada valor de “Descripción.”

1. Podemos agregar otra dimensión a nuestro conteo. Por ejemplo, arrastra la columna “Edad” al área vacía de “Columnas” para agregar más detalle a la tabla contando el número de descripciones por cada edad.

1. También puedes agregar la capacidad de selección de filtro a la tabla arrastrando la columna “Clase de Trabajo” al área de “Filtros.” Nota la nueva celda interactiva que aparece sobre nuestra tabla.

1. Haz clic en la flecha junto a “Todo,” luego selecciona “Nunca trabajado.” Esto cambiará la tabla para incluir solo a las personas con una Clase de Trabajo de “Nunca trabajado” en los cálculos. Puedes cambiar el filtro en cualquier momento para ver la tabla desde una perspectiva diferente.

¿Tienes muchos duplicados en tu libro de Excel? Aprende cómo encontrar y eliminar duplicados y limpiar tus hojas de cálculo.

5. Presentando tus Datos con PowerChart

Puedes crear más hojas de trabajo para analizar tus datos de diferentes maneras. Esta vez, vamos a crear un resultado más visualmente impresionante usando PowerChart.

1. Regresa a la ventana “Power Pivot para Excel.” Si está cerrada, puedes abrirla desde la ventana de Excel a través de “Datos -> Herramientas de Datos -> Administrar Modelo de Datos.”
2. Haz clic en “PivotTable -> PivotChart -> Nueva Hoja.”

1. En el panel de la derecha, haz clic en la fuente de datos (“kaggle_adult_csv”) para expandirla y mostrar todas sus columnas.
2. Arrastra y suelta las columnas de la fuente de datos en una de las cuatro áreas de abajo para construir el gráfico. Mueve la columna “Tipo de Ocupación” al área de “Eje (Categorías).”

1. Arrastra “Tipo de Ocupación” nuevamente, y esta vez suéltalo en el área de “Valores.” Notarás de inmediato que se genera automáticamente un gráfico de barras. Muestra el conteo de cada tipo de ocupación.

1. También puedes arrastrar la columna “Etnicidad” al área de “Leyenda (Series).” Verás instantáneamente un gráfico más granular que compara cuántos de cada etnicidad componen cada ocupación.

1. A continuación, podemos aplicar filtros a nuestra Leyenda para simplificar nuestro gráfico y solo mirar ciertos valores. Mueve el mouse sobre “Etnicidad” para que aparezca una flecha en el lado derecho. Haz clic en la flecha.

1. Haz clic en las casillas de verificación junto a los valores que deseas eliminar, dejando solo dos.

1. Vamos a intentar algo nuevo. En la sección de “Valores,” haz clic en “Conteo de Tipo de Ocupación,” luego haz clic en “Eliminar Campo.” Arrastra y suelta la columna “Edad” para reemplazarla.

1. Notarás que el valor se define automáticamente como “Suma de Edad,” pero eso no es muy útil en el mundo real. Cambiemos eso. Haz clic en él y selecciona “Configuración de Campo de Valor.”

1. Dado que la edad es un número, hay muchos cálculos que podemos aplicar a esta sección. Intenta seleccionar “Promedio” por ejemplo.

1. La visualización se actualizará para mostrar las edades promedio para cada ocupación por etnicidades específicas (que filtramos).

¿Quieres repasar los fundamentos de Excel? Tenemos una guía completa sobre todos los atajos de teclado para Excel.

Preguntas Frecuentes

¿Cuáles son los orígenes de Power Pivot?

Microsoft introdujo Power Pivot como un complemento separado proporcionado por SQL Server 2008 R2 de Microsoft, que fue lanzado en 2010. En ese entonces, se llamaba “PowerPivot” sin espacio. En 2013 fue renombrado como “Power Pivot.” Solo se convirtió en una función integrada de Excel a partir de 2016.

¿Qué son las Expresiones de Análisis de Datos y cómo se diferencian de las fórmulas de Excel?

Las Expresiones de Análisis de Datos son un lenguaje de fórmulas utilizado por Power Pivot en Excel y por Power BI. En esencia, es cómo escribes fórmulas para cálculos en las aplicaciones mencionadas: las declaraciones, la sintaxis y las funciones que usas.

La principal ventaja de DAX sobre las fórmulas de Excel es que está diseñado para operar sobre datos agregados. Las fórmulas estándar de Excel pueden realizar cálculos fila por fila (como calcular la suma de múltiples valores en una fila), pero las fórmulas DAX pueden operar columna por columna (como contar el número de un valor específico en una columna), o incluso sobre una tabla completa.

¿Cómo soluciono el error “Esta tabla fue creada por una consulta. Para cambiar esta tabla, cambia la consulta en su lugar”?

Este error en Excel Power Pivot ocurre cuando intentas modificar un modelo de datos dentro de Power Pivot cuando ese modelo de datos fue cargado originalmente a través de Power Query. No puedes hacer esto, así que debes modificarlo a través del método original de carga: Power Query.

En el lado derecho de Excel, haz clic en el ícono “Consultas y Conexiones”, luego busca y haz doble clic en tu fuente de datos de consulta para abrir la ventana donde puedes modificar la tabla.

Crédito de imagen: Pexels. Todas las capturas de pantalla por Brandon Li.