Power Query en Excel: obtener, transformar y combinar datos

Power Query es la parte de Excel que toma un archivo feo, lo deja presentable y recuerda cómo lo hizo. La próxima vez que llegue el mismo archivo, basta con pulsar «Actualizar». Está incluido en Excel desde la versión 2016 y en Microsoft 365, no cuesta nada aparte y, aun así, mucha gente sigue limpiando datos a mano cada mes.

Esta guía lo explica con un caso real: el inventario de un clúster de servidores que sale de la consola en bruto y tiene que acabar en un informe que lea la dirección. Los mismos pasos sirven para un extracto bancario, un reporte del ERP o la exportación de la mesa de servicio.

Dónde encaja esta guía

Es la tercera de cuatro guías construidas sobre el mismo inventario: un clúster de tres nodos con doce máquinas. Las dos primeras lo crearon y lo segmentaron; aquí ese dato cruza el puente hacia la ofimática.

1. Proxmox — la plataforma, los comandos y el reparto de recursos.
2. Firewall de Proxmox — VLAN, jerarquía de reglas y control del tráfico.
3. Power Query — está leyéndola: obtener, limpiar y combinar los datos.
4. Power BI — convertirlo en un tablero que se actualiza solo.

Qué es Power Query y dónde se abre

Es un motor de obtención y transformación de datos que vive dentro de Excel, en la pestaña Datos, bajo el grupo «Obtener y transformar datos». En versiones antiguas aparecía como un complemento llamado «Obtener y transformar»; hoy está integrado.

Lo que lo distingue de copiar y pegar es una sola idea: no transforma datos, graba instrucciones. Cada clic queda registrado como un paso con nombre. El resultado es un procedimiento repetible que cualquiera puede revisar, corregir y volver a ejecutar sobre el archivo del mes siguiente.

El mismo motor está en Power BI, en Power Automate y en Dataflows. Lo que aprenda aquí se aplica tal cual en las otras herramientas, que es justamente el argumento de la cuarta guía de esta serie.

El dato de partida: un inventario en bruto

El punto de partida es la exportación del clúster descrito en la primera guía: doce máquinas repartidas en tres nodos. Sale de una orden de consola y llega como un archivo de texto plano.

pvesh get /cluster/resources --type vm --output-format json > inventario.json

Ese archivo tiene los defectos de cualquier exportación de máquina: la memoria y el disco vienen en bytes, el nodo se repite en cada fila, hay columnas que no interesan a nadie y el estado viene en inglés. Esta es la tabla una vez ordenada, que es adonde queremos llegar.

Puede copiarla en una hoja en blanco y seguir la guía con ella. Los totales son 46 vCPU, 164 GiB de memoria y 7.200 GiB de disco, y conviene anotarlos: sirven para comprobar que ninguna transformación perdió filas por el camino.

Primer paso con Power Query: obtener los datos

En Datos > Obtener datos aparece la lista de orígenes. Los que se usan de verdad en una oficina son pocos.

La opción Carpeta merece un párrafo aparte, porque resuelve por sí sola un trabajo entero. Se apunta a una carpeta, se define cómo tratar un archivo, y Power Query aplica ese mismo tratamiento a todos los demás y los apila. Doce exportaciones mensuales se convierten en una tabla única; en enero se deja caer el archivo nuevo y se actualiza.

Al elegir el archivo aparece una vista previa con dos botones. Cargar mete los datos tal cual; Transformar datos abre el editor. Casi siempre hay que pulsar el segundo.

El editor de Power Query y sus pasos aplicados

El editor de Power Query tiene tres zonas: la lista de consultas a la izquierda, los datos en el centro y, a la derecha, el panel de pasos aplicados. Ese panel es el corazón del asunto.

Cada acción añade un paso, que se puede renombrar, reordenar, editar o borrar. Si el mes que viene el origen cambia de formato, no se rehace el trabajo: se corrige el paso que falla. Además, al hacer clic en cualquier paso intermedio se ve cómo estaban los datos en ese momento, lo que convierte el panel en una herramienta de diagnóstico.

Debajo funciona un lenguaje llamado M, visible en Vista > Editor avanzado. No hace falta escribirlo para trabajar, pero sí conviene saber que existe: es lo que se copia cuando alguien pide «pásame la consulta».

let
    Origen = Csv.Document(File.Contents(RutaInventario),
                          [Delimiter=",", Encoding=65001]),
    Encabezados = Table.PromoteHeaders(Origen),
    Tipos = Table.TransformColumnTypes(Encabezados,
              {{"vmid", Int64.Type}, {"maxmem", Int64.Type}}),
    RAM = Table.AddColumn(Tipos, "RAM (GiB)",
              each Number.Round([maxmem] / 1073741824, 0), Int64.Type)
in
    RAM

Las transformaciones de Power Query que resuelven el 90 %

Hay decenas de opciones en la cinta, pero en la práctica se repiten siempre las mismas diez.

Sobre el tipo de datos hay una advertencia que ahorra horas. Póngalo siempre como paso explícito y al final, no confíe en la detección automática. Un tipo mal detectado hace que una columna de VMID se interprete como fecha, y el error aparece semanas después, cuando ya nadie recuerda de dónde salió.

Anular la dinamización, la que casi nadie conoce

Es la transformación que más veces salva un archivo. Un reporte suele llegar con los meses en columnas —enero, febrero, marzo— porque así se lee cómodo. Para analizarlo hace falta lo contrario: una columna «Mes» y una columna «Valor».

Se seleccionan las columnas de meses y se pulsa Anular dinamización de columnas. Doce columnas se convierten en doce filas por máquina, y con eso la tabla ya sirve para una tabla dinámica. La variante Anular dinamización de otras columnas es todavía mejor, porque sigue funcionando cuando el origen añade un mes nuevo.

Combinar dos orígenes: anexar frente a combinar

Rara vez basta con un archivo. En el ejemplo hay un segundo: el reporte de las copias de seguridad, con una fila por máquina, su última copia correcta y el tamaño ocupado.

• Anexar consultas apila filas: dos archivos con las mismas columnas se convierten en una tabla más larga.
• Combinar consultas cruza columnas: trae a la tabla A los datos de la tabla B que coinciden por una clave.

Al combinar hay que elegir el tipo de unión, y ahí está toda la gracia. Una unión externa izquierda conserva las doce máquinas y deja vacío lo que no encuentre. Una anti-unión izquierda devuelve exactamente lo contrario: solo las filas sin pareja. Esa segunda opción convierte una consulta rutinaria en un control.

Ese cuadro es el que interesa a la dirección, y no existía en ninguno de los dos archivos de origen. Además aparece un dato que sorprende a mucha gente: se copian 926 GiB cuando hay 7.200 GiB asignados. La diferencia es el aprovisionamiento fino: el disco se reserva, pero solo ocupa lo que de verdad contiene.

Calcular y resumir sin salir de la consulta

Con los datos limpios, dos operaciones cierran el trabajo. La primera es la columna personalizada, para lo que el origen no trae.

// Disco en TiB, con un decimal
Number.Round([#"Disco (GiB)"] / 1024, 1)

// Clasificación por tamaño, en una columna condicional
if [#"RAM (GiB)"] >= 32 then "Grande"
else if [#"RAM (GiB)"] >= 8 then "Media"
else "Pequeña"

La segunda es Agrupar por, que hace en la consulta lo que una tabla dinámica hace en la hoja. Agrupando por nodo salen tres filas.

Los mismos datos agrupados por VLAN dan cinco filas: 8, 18, 10, 6 y 4 vCPU. Las dos sumas coinciden en 46, y esa coincidencia es la prueba de que la consulta está bien. Cuando dos agrupaciones distintas no cuadran, hay filas duplicadas o filtradas sin querer.

Parámetros: no deje la ruta escrita a fuego

Por defecto, la consulta guarda la ruta completa del archivo de origen. El día que el libro cambie de equipo o la carpeta se mueva, todo deja de funcionar. La solución es un parámetro, creado en Inicio > Administrar parámetros, que guarda la ruta en un solo sitio y se usa en todas las consultas.

Mejor todavía: leer la ruta de una celda de la hoja. Se convierte esa celda en tabla, se carga como consulta y se referencia. Así el usuario cambia la ruta sin abrir el editor.

Cargar el resultado y actualizarlo

Al pulsar Cerrar y cargar en… aparecen tres destinos, y elegir bien evita libros de cien megas.

Las consultas intermedias deben quedarse en «Solo crear conexión». Cargar a la hoja cada paso de un proceso de cinco consultas multiplica el tamaño del archivo sin aportar nada.

Para actualizar hay tres caminos: el botón Actualizar todo, la casilla «Actualizar al abrir el archivo» en las propiedades de la conexión, o una macro que lo haga junto con el resto del cierre mensual. Esa tercera vía está explicada en la guía sobre macros en Excel, y se resume en una línea de código.

Sub ActualizarInventario()
    ThisWorkbook.RefreshAll
End Sub

Errores frecuentes con Power Query

Sobre el rendimiento hay una regla general: filtrar pronto. Power Query intenta empujar los filtros hasta el origen, de modo que una base de datos devuelva solo lo necesario. Si el filtro va después de diez transformaciones, esa optimización se pierde.

Cuándo no usar Power Query

No sirve para escribir en el origen: solo lee. Tampoco es la herramienta para un cálculo que depende de la fila que el usuario tenga seleccionada, ni para un dato que hay que teclear a mano cada día. Y si el archivo se abre una sola vez en la vida, preparar la consulta cuesta más de lo que ahorra.

La pregunta que decide es siempre la misma: ¿esto va a volver a pasar? Si la respuesta es sí, la consulta se paga la primera vez que se repite.

Qué se hace después con estos datos

Una consulta limpia no es el final, es el principio. El mismo inventario, ya transformado, alimenta cuatro resultados distintos sin volver a tocarlo.

Ese es el trabajo real de una plataforma ofimática bien montada: un solo dato de origen y cuatro salidas que no se contradicen entre sí. La administración de las licencias y de los accesos que lo sostienen es parte de la gestión de plataformas.

Preguntas frecuentes sobre Power Query

¿Hay que pagar algo aparte?

No. Viene incluido en Excel para Windows desde 2016 y en Microsoft 365. En Excel para Mac la funcionalidad es más limitada, y en la versión web solo se pueden actualizar consultas, no crearlas con toda la libertad del escritorio.

¿Hay que saber programar?

No para empezar. Toda la limpieza habitual se hace con la cinta de opciones. El lenguaje M aparece solo cuando se quiere algo que el menú no ofrece, y para entonces ya se entiende la lógica de los pasos.

¿En qué se diferencia de una tabla dinámica?

Van en orden, no compiten. Power Query prepara los datos; la tabla dinámica los resume. Intentar resumir datos sucios es lo que produce esos informes con tres versiones del mismo nombre de sede.

¿Cuántas filas aguanta?

Si se carga a una hoja, el límite es el de Excel: 1.048.576 filas. Si se carga al modelo de datos, se trabaja con varios millones sin problema, porque el motor comprime en memoria y no depende de la cuadrícula.

Qué llevarse sobre Power Query

Power Query convierte una tarea manual de cada mes en un botón. Graba pasos en vez de resultados, combina orígenes que nadie había cruzado y deja la evidencia de cómo se llegó a cada cifra. Para un área de TI es, además, la forma más rápida de que un dato técnico —doce máquinas, tres nodos, 926 GiB copiados— llegue a la dirección en un formato que se pueda leer sin explicaciones.

La última guía de la serie da el paso siguiente: llevar ese mismo modelo a un tablero que se actualiza solo y se consulta desde el teléfono.