Power Query: Importa y transforma datos como un profesional

Si pasas horas cada semana descargando archivos CSV, eliminando filas vacías, dando formato a fechas y escribiendo complejas fórmulas anidadas solo para preparar tus datos para el análisis, estás trabajando más de lo necesario. Te damos la bienvenida a Power Query: la herramienta de automatización de datos más potente integrada directamente en Microsoft Excel.

A menudo conocido como "Obtener y transformar datos", Power Query te permite conectarte a casi cualquier origen de datos, limpiar y dar forma a la información, y cargarla en tu hoja de cálculo. ¿Lo mejor de todo? Registra tus pasos. La próxima vez que recibas datos nuevos, no tendrás que repetir el trabajo manual; simplemente haz clic en Actualizar.

En esta guía completa, exploraremos qué es Power Query, cómo navegar por su interfaz y analizaremos un ejemplo práctico de cómo transformar un conjunto de datos desordenado en información limpia y lista para el análisis.

¿Qué es Power Query?

Power Query es un motor de conexión y preparación de datos. En el mundo de la gestión de bases de datos, este proceso se conoce como ETL: Extraer, Transformar y Cargar (por sus siglas en inglés, Extract, Transform, Load).

• Extraer: Obtén datos de diversas fuentes (libros de Excel, archivos CSV y de texto, páginas web, bases de datos SQL o incluso servicios en la nube).
• Transformar: Limpia y da forma a los datos. Esto incluye filtrar filas, cambiar tipos de datos, dividir texto, combinar tablas y crear columnas calculadas.
• Cargar: Exporta los datos pulidos directamente a una tabla de Excel, una tabla dinámica o al modelo de datos de Power Pivot.

Tradicionalmente, los usuarios de Excel dependían de una combinación de funciones como TRIM, PROPER, SUBSTITUTE y VLOOKUP junto con copiar y pegar manualmente para realizar estas tareas. Power Query reemplaza ese tedioso flujo de trabajo con una interfaz visual y fácil de usar.

Por qué deberías aprender Power Query

Si todavía tienes dudas sobre si aprender una nueva herramienta de Excel, aquí tienes por qué dominar Power Query supondrá un antes y un después para tu productividad:

• No requiere programación: Aunque Power Query funciona con un lenguaje de fórmulas llamado "M", puedes realizar el 95% de las transformaciones de datos utilizando solo los botones de la cinta de opciones y los menús del botón derecho.
• Automatización sin VBA: No necesitas saber escribir macros para automatizar tareas repetitivas. Power Query recuerda tus "Pasos aplicados" y los aplica a los nuevos datos automáticamente.
• Maneja grandes volúmenes de datos (Big Data): Excel tiene un límite físico de 1.048.576 filas. Power Query puede conectarse a conjuntos de datos con millones de filas, agregar los datos en segundo plano y cargar solo los resultados resumidos en tu hoja de cálculo.
• Edición no destructiva: Power Query nunca altera tu fuente de datos original. Crea una conexión, lee los datos y genera una copia limpia.

Primeros pasos: El Editor de Power Query

Para acceder a Power Query, abre un libro de Excel en blanco y ve a la pestaña Datos en la cinta de opciones. Busca el grupo Obtener y transformar datos en el extremo izquierdo.

Desde aquí, puedes hacer clic en Obtener datos para ver un menú desplegable con los orígenes de datos disponibles. Una vez que selecciones un archivo y hagas clic en "Transformar datos", Excel abrirá el Editor de Power Query en una ventana nueva. Esta interfaz consta de cuatro áreas principales:

1. La cinta de opciones: Situada en la parte superior, contiene pestañas como Inicio, Transformar, Agregar columna y Vista. Aquí es donde se encuentran todas tus herramientas.
2. El panel de Consultas: Ubicado a la izquierda, enumera todas las conexiones de datos activas en tu libro.
3. La vista previa de datos: La cuadrícula central que muestra una vista previa en vivo del aspecto de tus datos en el paso seleccionado actualmente.
4. El panel de Configuración de la consulta (Pasos aplicados): Situado a la derecha. Esta es la parte más importante del editor. Cada vez que cambias algo, Power Query lo registra aquí como un paso secuencial. Puedes eliminar, reordenar o editar estos pasos en cualquier momento.

Guía paso a paso: Limpieza de datos de ventas desordenados

Veamos un ejemplo práctico del mundo real. Imagina que exportas un informe de ventas semanal desde el CRM de tu empresa. La exportación sin procesar es un desastre: contiene encabezados innecesarios, cadenas de texto combinadas y formatos inconsistentes.

Aquí tienes una muestra de nuestros datos sin procesar y desordenados:

Exportación del sistema: Informe de ventas Q3	Column2	Column3
Generado el: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

Si utilizáramos fórmulas tradicionales, tendríamos que usar LEFT, RIGHT, FIND y VALUE para extraer los nombres de los representantes y arreglar los números. En su lugar, usaremos Power Query.

Paso 1: Importar los datos

Guarda los datos desordenados como un archivo CSV o de Excel. Abre un nuevo libro de Excel, ve a Datos > Obtener datos > De un archivo y selecciona tu archivo. Cuando aparezca la ventana de vista previa, haz clic en Transformar datos. Se abrirá el Editor de Power Query.

Paso 2: Quitar filas superiores y promover encabezados

Las dos primeras filas de nuestros datos son metadatos de exportación del sistema, no registros de datos reales. Necesitamos deshacernos de ellas.

• En la pestaña Inicio, haz clic en Quitar filas > Quitar filas superiores. Introduce "2" y haz clic en Aceptar.
• Ahora, nuestros encabezados de columna reales se encuentran en la fila 1. Haz clic en el botón Usar la primera fila como encabezado en la pestaña Inicio. Al instante, "Rep_ID_Name", "Order_Date" y "Revenue" se convertirán en tus títulos de columna oficiales.

Paso 3: Dividir la columna del representante

La columna "Rep_ID_Name" contiene tanto el número de identificación como el nombre del empleado, separados por un guion.

• Haz clic con el botón derecho en el encabezado de la columna "Rep_ID_Name".
• Selecciona Dividir columna > Por delimitador.
• Elige "Personalizado" y escribe un guion ( - ). Haz clic en Aceptar.
• Power Query dividirá los datos en dos columnas. Ahora puedes hacer doble clic en los nuevos encabezados de columna para cambiarles el nombre a "Rep_ID" y "Rep_Name".

Para limpiar los guiones bajos en el nombre de Bob (Bob_Jones), haz clic con el botón derecho en la columna Rep_Name, elige Reemplazar los valores, escribe un guion bajo (_) en el cuadro "Valor que buscar" y deja "Reemplazar con" en blanco o añade un espacio. Haz clic en Aceptar.

Paso 4: Estandarizar los tipos de datos

¿Notas cómo nuestras fechas y los ingresos están en formatos completamente diferentes? Power Query facilita la estandarización de esto.

• Haz clic en el pequeño icono situado en el lado izquierdo del encabezado de la columna "Order_Date" (probablemente diga "ABC" o "Cualquiera"). Cámbialo a Fecha. Power Query traduce automáticamente los diferentes formatos de fecha en una fecha estándar unificada.
• Haz clic en el icono junto a "Revenue" y cámbialo a Moneda (o Número decimal). Esto elimina los signos de dólar y da formato a todo de manera limpia.

Paso 5: Agregar una columna condicional

Supongamos que queremos categorizar las ventas superiores a $1.000 como "High Value" (Alto valor). En lugar de escribir una compleja función IF como =IF(C2>=1000, "High Value", "Standard") en Excel, podemos usar la interfaz de Power Query.

Ve a la pestaña Agregar columna y haz clic en Columna condicional. Configura las reglas: Si [Revenue] es mayor o igual que 1000, la salida es "High Value", de lo contrario "Standard". Entre bastidores, Power Query genera el siguiente código M para este paso:

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

Combinar consultas: La alternativa moderna a VLOOKUP

Una de las tareas más comunes en el análisis de datos es combinar tablas. Si tienes una tabla separada que contiene la región de cada representante de ventas, normalmente consultarías nuestra guía completa de VLOOKUP para incorporar esos datos.

Sin embargo, ejecutar miles de fórmulas VLOOKUP o INDEX y MATCH puede ralentizar drásticamente tu libro de Excel. En Power Query, se utiliza la función Combinar consultas.

Simplemente importa ambas tablas a Power Query, selecciona tu tabla de ventas principal y haz clic en Combinar consultas en la pestaña Inicio. Selecciona la segunda tabla (la tabla de Regiones), haz clic en la columna coincidente en ambas tablas (por ejemplo, "Rep_ID") y haz clic en Aceptar. Power Query realiza el equivalente a un VLOOKUP ultrarrápido en segundos, independientemente de si tienes diez filas o diez millones.

Anular dinamización de datos: El arma secreta de Power Query

A menudo, recibes datos que ya están agrupados en una estructura similar a la dinámica (por ejemplo, los meses distribuidos a lo largo de las columnas: Ene, Feb, Mar, Abr). Aunque esto es fácil de leer para los humanos, es terrible para crear gráficos o tablas dinámicas.

Selecciona tus columnas identificadoras (como el nombre del representante), haz clic derecho en el encabezado y elige Anular dinamización de otras columnas. Power Query transforma al instante tus datos anchos y tabulares cruzados en un diseño tabular plano con una nueva columna "Atributo" (Mes) y "Valor" (Ventas). Hacer esto con fórmulas estándar de Excel es casi imposible, lo que convierte a la anulación de dinamización en una de las funciones más celebradas de Power Query.

Cargar los datos transformados

Una vez que tus datos estén perfectamente limpios, es hora de enviarlos de vuelta a Excel.

En la pestaña Inicio, haz clic en Cerrar y cargar. De forma predeterminada, esto cargará tus datos transformados en una tabla de Excel verde completamente nueva en una nueva hoja de cálculo. Si prefieres enviar los datos directamente a tu fase de análisis, puedes hacer clic en la flecha desplegable, elegir Cerrar y cargar en... y seleccionar un Informe de tabla dinámica en su lugar. Si necesitas repasar cómo construir estos resúmenes, echa un vistazo a nuestro tutorial sobre cómo crear tablas dinámicas para principiantes.

La magia del botón "Actualizar"

El verdadero poder de Power Query se hace evidente la próxima semana cuando recibas una nueva exportación de ventas sin procesar. ¡No repitas los pasos anteriores!

Simplemente guarda el nuevo archivo CSV sobre el antiguo (mantén exactamente el mismo nombre de archivo y ubicación de carpeta). A continuación, abre tu libro de Excel, haz clic derecho en cualquier lugar de tu tabla de datos limpios y haz clic en Actualizar.

Power Query accede al archivo, vuelve a aplicar cada uno de los pasos (eliminar filas, promover encabezados, dividir columnas, reemplazar texto, verificar condiciones y combinar tablas) y actualiza el resultado final en una fracción de segundo. Este es un componente vital de los flujos de trabajo de automatización de Excel.

Simplifica tu trabajo con datos gracias a la IA

Aunque Power Query maneja las transformaciones estructurales de manera brillante, a veces necesitas una lógica condicional específica o un análisis de texto complejo que requiere fórmulas avanzadas de Excel o código M personalizado. En lugar de buscar respuestas en foros, puedes aprovechar la inteligencia artificial.

Si te cuesta escribir el cálculo perfecto para una columna personalizada, GPTExcel es el compañero ideal. Solo tienes que describir lo que intentas lograr en lenguaje natural (por ejemplo, "Necesito una fórmula para extraer solo los números de una cadena de texto mixta") y GPTExcel generará al instante la fórmula o el código M correctos. Combinar Power Query con la IA para la limpieza de datos te proporciona un conjunto de herramientas imparable para el análisis de datos.

Preguntas frecuentes

¿Power Query modifica mi archivo de datos original?

No. Power Query crea una conexión unidireccional con tus datos de origen. Lee los datos, aplica las transformaciones en la memoria y genera un nuevo resultado en Excel. Tu CSV, base de datos o libro de trabajo original permanece completamente intacto y seguro.

¿Puedo usar Power Query en un Mac?

Sí, Microsoft ha mejorado significativamente el soporte de Power Query en Excel para Mac. Aunque la versión para Mac tradicionalmente carecía de algunos de los conectores avanzados y funciones de interfaz de usuario disponibles en Windows, ahora puedes conectarte a archivos locales, bases de datos y actualizar consultas existentes sin problemas en las versiones modernas de Microsoft 365.

¿Cuál es la diferencia entre Combinar y Anexar en Power Query?

Combinar (Merge) es el equivalente a un VLOOKUP o INDEX/MATCH. Se utiliza para agregar nuevas columnas de datos haciendo coincidir un ID común entre dos tablas. Anexar (Append) es como copiar y pegar datos en la parte inferior de una hoja. Se usa para apilar tablas una encima de la otra, agregando nuevas filas (por ejemplo, combinando las ventas de enero y las ventas de febrero).

¿Por qué ha fallado la actualización de mi Power Query?

La razón más común por la que falla la actualización de una consulta es que el archivo de origen se ha movido, se le ha cambiado el nombre o se ha eliminado. Otro problema frecuente es que haya cambiado un encabezado de columna en los datos sin procesar (por ejemplo, el sistema cambió "Revenue" por "Total Revenue"). Puedes solucionar esto abriendo el Editor de Power Query, yendo al panel de Pasos aplicados y actualizando el paso Origen o cambiando el nombre de la columna en la lógica de tu paso.