Contenido del curso
Cómo automatizar limpieza de datos con Power Query
Automatizar la carga y limpieza de datos en Power Query te ahorra horas de trabajo repetitivo cada mes. Aprenderás a importar un ERP contable, quitar subtotales, convertir monedas y combinar tablas para construir estados financieros confiables. Ideal para analistas financieros que trabajan con Excel.
La gran ventaja frente a hacerlo todo en Excel es simple: con Power Query solo das clic en actualizar y toda la limpieza se repite sola en febrero, marzo, abril o el año que quieras. Ese es el corazón de este ejercicio.
Por qué usar Power Query en lugar de limpiar en Excel
Cada vez que descargas un archivo del ERP, la limpieza manual toma muchísimo tiempo. Power Query graba cada paso y lo reutiliza, así que el proceso repetitivo se vuelve un solo clic.
Empiezas en la pestaña de datos, obtienes datos desde un libro de Excel y eliges la hoja de trabajo. En este caso arrancamos con la pestaña de PnL mensual del RP contable y pulsas transformar datos para entrar al editor.
¿Qué hace Power Query al actualizar los datos? Repite automáticamente todos los pasos de limpieza que grabaste (quitar filas, promover encabezados, combinar tablas) sobre el archivo nuevo, sin que tengas que rehacer nada manualmente.
Cómo quitar filas y promover encabezados
Al importar aparece un dato extra que viene cuando descargas el archivo, así que lo quitas. El orden es sencillo y siempre igual:
Después de dejar los encabezados listos, toca el detalle más fácil de olvidar: los subtotales.
Cómo eliminar subtotales según el tipo de dato
Aquí viene lo interesante. Los subtotales suelen venir marcados con un código que termina en 99, y para filtrarlos necesitas que la columna esté en el tipo correcto.
Si la columna está como número, no aparece la opción de filtro que necesitas. La solución es cambiar el tipo a texto, porque eso define qué filtros te muestra Power Query [01:40]. Luego vas a filtros de texto y eliges no termina con 99 para borrar los subtotales de golpe [02:05].
¿Por qué cambiar una columna de número a texto en Power Query? Porque el tipo de dato determina qué filtros están disponibles. Los filtros de texto (como "no termina con") solo aparecen si la columna es texto, no número.
Cómo convertir dólares a pesos con una columna personalizada
Algunos montos vienen en dólares y eso complica el análisis. La solución es crear una columna nueva que unifique todo en pesos mexicanos.
Vas a agregar columna, columna personalizada, la nombras monto_MXN y escribes una fórmula con IF, igual que en Excel pero en inglés dentro de Power Query. La lógica es: si la moneda es dólares, multiplica el monto por el tipo de cambio de 17.2; en caso contrario, deja el monto tal cual [03:00].
Con esto todos los importes quedan en la misma moneda y dejan de complicarte la vida.
Cómo combinar consultas con el diccionario financiero
Para enriquecer tus datos, vas a Inicio y luego a combinar consultas. Conectas tu información con el diccionario financiero usando código con código como campo de unión [03:35].
Usas siempre externa izquierda para asegurarte de traer todos tus datos. Una vez conectada la tabla, eliges qué columnas conservar del diccionario:
El campo presupuesto lo puedes dejar o quitar según lo necesites. Y recuerda desmarcar la opción de quitar el prefijo para que las columnas conserven su nombre.
Cómo crear el monto ajustado con el signo correcto
Este es el término que vas a usar muchísimo. El monto ajustado define si un valor entra positivo o negativo en tus estados financieros.
Creas otra columna personalizada llamada monto ajustado, y multiplicas el monto MXN por el signo que trajiste del diccionario [04:30]. Un dato que parecía puro positivo se convierte en negativo según el signo definido, y eso permite que tu estado de resultados traiga los valores correctos.
¿Qué es el monto ajustado en un modelo financiero? Es el monto en pesos multiplicado por un signo (positivo o negativo) definido en el diccionario financiero, para que cada concepto sume o reste correctamente en los estados financieros.
Después agregas dos columnas de contexto para rastrear el origen de cada registro:
Estas etiquetas cobran sentido más adelante cuando mezclas datos reales con presupuesto.
Cómo seleccionar columnas finales y cargar en Excel
Después de tanto ajuste, quedas con muchas columnas y no todas sirven. Vas a Inicio, elegir columnas, y te quedas solo con lo esencial.
Conservas sucursal, código, mes, estado, rubro, subrubro, nombre análisis, monto ajustado, tipo y fuente. Quitas moneda, monto, monto MXN, signo e incluir, porque el signo solo servía para la multiplicación [06:00]. El monto ajustado es el más importante porque trae el valor real que usarás en los estados financieros.
Finalmente das cerrar y cargar, y la tabla PnL mensual regresa a Excel ya limpia. Luego repites el mismo proceso con la hoja de punto de venta, donde cargas las ventas diarias con los mismos pasos.
El reto de la clase es que tú repliques este procedimiento con la información de punto de venta y con la de presupuesto 2024. En el archivo de la clase tienes el paso a paso completo del ERP y del punto de venta, con pasos muy similares que reconocerás: filas superiores quitadas, encabezados promovidos, tipo cambiado y consultas combinadas.
¿Ya intentaste replicar la limpieza con tu propia base de datos? Cuéntame en los comentarios cómo te fue.
HERISSON ANDRES TRUJILLO ANDRADE
Estudiante
Jose Emiliano Cajigal Duran
EstudianteMe parece que debes tener seleccionada la columna de Código en vez de moneda al lado derecho en la sección de "Columnas disponibles".
Hector Duval García
EstudianteSi cambias de carpeta, renombras el archivo o te envían una nueva versión con otra ruta, tus consultas fallarán al intentar actualizar. Para solucionarlo, debes ir directamente a la configuración de origen en tu herramienta de transformación de datos. Al hacer clic en el ícono de engranaje del paso Origen, podrás examinar y seleccionar la nueva ubicación de tu archivo. Una vez que sustituyas esta ruta, toda la limpieza y transformación que ya habías programado se aplicará automáticamente al nuevo archivo. Es una excelente práctica mantener tus archivos fuente en una carpeta estandarizada o en la nube, pero si el cambio es inevitable, esta simple actualización de ruta salvará todo tu trabajo previo. Después, solo necesitas presionar Alt + F5 para que tus tablas dinámicas reflejen la información fresca.
Arturo Bustos Carrillo
Estudiante¿Cuándo debería usar una vista Year to Date?
Deberías utilizar una vista Year to Date (YTD) cuando necesites evaluar el desempeño acumulado de tu negocio desde el inicio del año hasta el momento actual, sin distraerte con las fluctuaciones de cada mes individual. Es la herramienta perfecta para responder a la pregunta: "¿Cómo vamos en el año en general?". A diferencia de un P&L mensual que desglosa la información periodo por periodo, el YTD consolida los montos ajustados para darte una visión macro. Esto es vital para los directivos o inversionistas que buscan entender si la empresa está en camino de cumplir sus metas anuales. Al configurar tu tabla dinámica, simplemente omites los meses en las columnas y filtras por el año en curso, obteniendo un resumen limpio y directo del progreso financiero acumulado.
Dayana Cárdenas
Estudiante¿Por qué es mejor anexar la información?
Anexar (o apilar) es la estrategia ideal cuando tienes bases de datos con la misma estructura, como tu P&L y tu presupuesto, y necesitas unificarlas en una sola gran base maestra. Imagina que estás apilando bloques de Lego uno sobre otro; tu base de datos crece hacia abajo agregando más filas. Esto permite que las tablas dinámicas lean toda la información de forma continua. Por el contrario, combinar funciona más como un BUSCARV, agregando columnas hacia la derecha para enriquecer los datos existentes. Al anexar, garantizas que al construir tus reportes financieros, puedas comparar diferentes escenarios (como lo real frente a lo proyectado) usando exactamente las mismas categorías y rubros sin romper la estructura de tu modelo.
isaac aguilar
Estudianteen que consiste combinar consultas, para ue sirve?
Combinar consultas es básicamente el arte de unir dos tablas distintas basándote en un dato que tienen en común, como si fuera un puente entre ellas. Sirve para enriquecer tu información, trayendo columnas de una tabla hacia otra sin tener que copiar y pegar manualmente.
Imagina que tienes una lista de ventas y otra de clientes; ¿qué crees que pasaría si intentas unirlas sin un identificador único que las relacione?
HERISSON ANDRES TRUJILLO ANDRADE
Estudiantedime tu
Si cambias el nombre del archivo, Power Query perderá el rastro. La consulta buscará un archivo que ya no existe en esa ruta y te lanzará un error de "Fuente no encontrada". Tendrías que entrar al Editor de Power Query y actualizar la ruta manualmente en el paso de "Origen".
¿Por qué crees que Power Query es tan estricto con la ubicación y el nombre del archivo?
HERISSON ANDRES TRUJILLO ANDRADE
Estudiante¿si deseo actualizar los datos de mi diccionario, lo debo hacer desde el archivo de excel fuente que está dentro de la carpeta de donde el power query actualiza la información?
Exacto. Power Query simplemente sigue las instrucciones que le diste sobre la ruta de ese archivo. Si modificas el diccionario en su ubicación original, la próxima vez que presiones "Actualizar", Power Query leerá los cambios automáticamente.
¿Qué crees que sucedería con tu reporte si cambias el nombre del archivo del diccionario sin actualizar la ruta en Power Query?
sergio galvan
Estudiante¿Cómo calculo variaciones entre Real y Presupuesto?
La mejor forma de hacerlo es aprovechando la funcionalidad nativa de las tablas dinámicas llamada Mostrar valores como, evitando por completo crear fórmulas manuales por fuera de la tabla. Cuando colocas tu monto ajustado en la sección de valores, puedes configurarlo para mostrar la Diferencia de o el Porcentaje de la diferencia de. Al hacer esto, Excel te pedirá un campo base (que sería tu tipo de dato) y un elemento base (el presupuesto). Automáticamente, la tabla calculará la variación exacta en dinero y en porcentaje de tus resultados reales frente a lo que habías proyectado. Esta técnica no solo es más rápida, sino que hace que tu reporte sea completamente dinámico; si filtras por una sucursal distinta o cambias el año, las variaciones se recalcularán al instante sin errores de referencia.
MADELEINE LIZBETH SHUNCO CRUZ
Estudiante¿Por qué es mejor anexar la información?
Anexar (o apilar) es la estrategia ideal cuando tienes bases de datos con la misma estructura, como tu P&L y tu presupuesto, y necesitas unificarlas en una sola gran base maestra. Imagina que estás apilando bloques de Lego uno sobre otro; tu base de datos crece hacia abajo agregando más filas. Esto permite que las tablas dinámicas lean toda la información de forma continua. Por el contrario, combinar funciona más como un BUSCARV, agregando columnas hacia la derecha para enriquecer los datos existentes. Al anexar, garantizas que al construir tus reportes financieros, puedas comparar diferentes escenarios (como lo real frente a lo proyectado) usando exactamente las mismas categorías y rubros sin romper la estructura de tu modelo.
Darlinson Felipe Polania Camacho
EstudianteLA SECCIO DE ⭐ DESPIVOTAR Seleccionar SOLO columna Concepto → Transformar → Despivotar otras columnas. Meses pasan de columnas a filas.
No la encuentro en la opcion de power quwry cual es el paso preciso para que las columana se pasen a filas es la misma de trasponer?
No, no es lo mismo. Trasponer voltea toda la tabla como un espejo, mientras que Anular dinamización (o Unpivot) es una operación quirúrgica para normalizar datos.
En la pestaña Transformar, busca el botón que dice Anular dinamización de columnas. Si seleccionaste la columna "Concepto" antes, usa la opción Anular dinamización de otras columnas. ¿Ves ese icono que parece una pequeña tabla siendo reorganizada?
camilo plata
Estudiante
