Lección 11 de 12 · Nivel 3: Avanzado
Power Query en Excel: importar y limpiar datos automáticamente
En esta lección
- Importar datos de archivos CSV, otros libros y carpetas completas.
- Limpiar datos (espacios, tipos, columnas sobrantes) sin fórmulas y sin repetir el trabajo cada mes.
- Unir tablas y "despivotar" informes para poder analizarlos.
- Actualizar todo tu informe con un solo clic.
Si cada semana descargas un reporte del sistema, borras columnas, corriges formatos, pegas los datos en tu archivo y rehaces el resumen, Power Query te va a devolver horas. Es una herramienta integrada en Excel (desde la versión 2016 en Windows, y con funciones más limitadas en Mac) que graba cada paso de limpieza y lo repite automáticamente cuando llegan datos nuevos.
La idea clave: pasos que se repiten solos
Con fórmulas o a mano, limpiar datos es trabajo que se pierde: el mes que viene hay que hacerlo otra vez. En Power Query cada acción (quitar una columna, cambiar un tipo, filtrar filas) queda registrada en la lista de Pasos aplicados. Cuando actualizas, Power Query vuelve a leer el origen y repite todos los pasos en orden.
Paso 1: obtener los datos
Todo empieza en Datos → Obtener datos (o el grupo Obtener y transformar datos):
| Origen | Dónde | Uso típico |
|---|---|---|
| Tabla o rango del mismo libro | Desde tabla o rango | Limpiar datos que ya tienes en Excel |
| Archivo de texto o CSV | Desde archivo → Desde texto/CSV | Exportaciones de sistemas, bancos, tiendas online |
| Otro libro de Excel | Desde archivo → Desde libro | Reportes de otras áreas |
| Una carpeta | Desde archivo → Desde carpeta | Unir todos los archivos mensuales en una sola tabla |
| Web, bases de datos, SharePoint | Desde otras fuentes | Datos corporativos o publicados en internet |
Al elegir el origen, verás una vista previa. Pulsa Transformar datos (no "Cargar") para abrir el Editor de Power Query.
Paso 2: limpiar en el Editor
El editor se parece a Excel, pero cada clic es un paso grabado. Las transformaciones más útiles:
| Problema | Solución en Power Query |
|---|---|
| Los encabezados están en la segunda fila | Inicio → Quitar filas → Quitar filas superiores y luego Usar la primera fila como encabezado |
| Columnas que no necesitas | Selecciona las que sí quieres → clic derecho → Quitar otras columnas (si el origen añade columnas nuevas, no te rompen el informe) |
| Números o fechas leídos como texto | Clic en el icono del encabezado → elige el tipo. Para fechas de otros países: Cambiar tipo → Usando configuración regional |
| Espacios sobrantes | Transformar → Formato → Recortar |
| Mayúsculas inconsistentes | Formato → Poner En Mayúsculas Cada Palabra |
| Un código y un nombre juntos ("PE-01 Lima") | Dividir columna → Por delimitador |
| Filas vacías o de totales | Filtra desde la flecha del encabezado, o Quitar filas → Quitar filas en blanco |
| Celdas vacías que deberían repetir el valor de arriba | Transformar → Rellenar → Hacia abajo |
| Valores mal escritos ("Nrte") | Clic derecho → Reemplazar valores |
No hay Ctrl + Z como en Excel: borra el paso con la ✕ en la lista de Pasos aplicados. Puedes hacer clic en cualquier paso anterior para ver cómo estaban los datos en ese momento.
1. ¿Por qué es mejor "Quitar otras columnas" que "Quitar columnas"?
Al indicar qué columnas conservar, cualquier columna nueva del origen se descarta automáticamente.
Despivotar: convertir un informe en datos
Muchos reportes vienen "en formato informe", con un mes por columna:
| Producto | Ene | Feb | Mar |
|---|---|---|---|
| Café | 120 | 135 | 150 |
| Té | 80 | 75 | 90 |
Así no se puede filtrar por mes ni hacer una tabla dinámica cómoda. Selecciona la columna Producto → clic derecho → Anular dinamización de otras columnas. Obtienes una lista con las columnas Producto, Atributo (el mes) y Valor: el formato correcto para analizar (las reglas de la lección 1).
2. Un reporte tiene una columna por cada mes del año. ¿Qué transformación lo convierte en una lista analizable?
"Anular dinamización" (unpivot) convierte las columnas de meses en filas: una fila por producto y mes.
Combinar y anexar consultas
- Anexar consultas pone una tabla debajo de otra (ventas de enero + ventas de febrero). Requiere que tengan las mismas columnas.
- Combinar consultas une dos tablas por una columna común, como un
BUSCARVmasivo: a la tabla de ventas le añades la región y la categoría desde la tabla de productos. Elige el tipo de combinación Externa izquierda para conservar todas las ventas aunque algún producto no exista en el catálogo.
El truco estrella: unir todos los archivos de una carpeta
- Guarda todos los reportes mensuales (con la misma estructura) en una carpeta.
- Obtener datos → Desde archivo → Desde carpeta → elige la carpeta → Combinar y transformar datos.
- Aplica la limpieza una vez.
- El mes que viene, deja el archivo nuevo en la carpeta y pulsa Actualizar todo. El nuevo mes se suma solo.
Agrupar por
Transformar → Agrupar por resume los datos dentro de Power Query: por ejemplo, la suma de importe y el número de pedidos por cliente. Útil cuando la tabla original es enorme y solo necesitas el resumen.
Paso 3: cargar y actualizar
Inicio → Cerrar y cargar en… te deja elegir:
- Tabla en una hoja nueva (lo más común).
- Solo crear conexión: la consulta existe pero no se muestra. Útil para consultas intermedias.
- Agregar al modelo de datos: para trabajar con millones de filas y relacionar tablas, la base de Power Pivot y de Power BI.
Para actualizar: Datos → Actualizar todo (Ctrl + Alt + F5). Si sobre la tabla resultante tienes tablas dinámicas, actualízalas también (el mismo botón lo hace).
Si la consulta lee un archivo o carpeta de tu computadora y lo mueves o se lo envías a otra persona, la ruta deja de existir. Cámbiala en Datos → Obtener datos → Configuración de origen de datos. Para equipos, guarda los orígenes en una carpeta compartida (OneDrive, SharePoint o un servidor).
Un vistazo al lenguaje M
Cada paso que haces con el mouse se escribe en un lenguaje llamado M. Puedes verlo en Vista → Editor avanzado. No necesitas aprenderlo para usar Power Query, pero leerlo te ayuda a entender y ajustar tus consultas. Un paso se ve así:
#"Tipo cambiado" = Table.TransformColumnTypes(Origen, {{"Fecha", type date}, {"Importe", type number}})3. Llega el reporte de un mes nuevo a tu carpeta. ¿Qué haces para incluirlo en tu informe?
La consulta de carpeta lee todos los archivos y repite los pasos al actualizar.
4. Quieres añadir la categoría de cada producto a tu tabla de ventas desde otra tabla. ¿Qué usas?
Combinar une tablas por una columna común (como un BUSCARV). Anexar las apila una debajo de otra.
Reto: automatiza un reporte mensual
- Crea tres archivos CSV (enero, febrero y marzo) con las columnas Fecha, Producto, Cantidad, Precio. Mete a propósito espacios sobrantes y algún producto en minúsculas.
- Guárdalos en una carpeta y combínalos con Desde carpeta.
- Limpia: recorta espacios, pon mayúsculas iniciales, cambia los tipos y añade una columna personalizada
[Cantidad] * [Precio](Agregar columna → Columna personalizada). - Carga el resultado y crea una tabla dinámica de ventas por mes y producto.
- Crea un cuarto CSV (abril), déjalo en la carpeta y pulsa Actualizar todo.
¿Cómo sé que funcionó?
Después de actualizar, abril debe aparecer en la tabla dinámica sin que hayas tocado nada más. Si un producto aparece duplicado ("Café" y "café "), te faltó recortar o normalizar mayúsculas antes de cargar.
Resumen
- Power Query graba cada paso de limpieza y lo repite al actualizar.
- Quitar otras columnas, cambiar tipos, recortar, dividir y rellenar resuelven la mayoría de los problemas.
- Anular dinamización convierte informes en datos analizables; Combinar une tablas; Anexar las apila.
- Con "Desde carpeta", un archivo nuevo se incorpora con un clic.
En la última lección automatizarás tareas repetitivas con macros y darás tus primeros pasos en VBA.
Crea tu cuenta, marca tus avances y descarga el certificado al terminar el curso.