Automatiza tus Reportes en Excel con Power Query + M

La gestión diaria de reportes en Excel suele implicar tareas repetitivas de copiado, pegado y limpieza manual de datos. Este artículo presenta un flujo de trabajo basado en Power Query y Lenguaje M que permite:

1. Estandarizar la estructura de cada reporte individual para hacerlos aptos para tablas dinámicas, filtros y ordenamientos.
2. Consolidar automáticamente múltiples reportes (uno por hoja) en una única tabla maestra.
3. Actualizar el concentrado sin intervención adicional cuando se agregan nuevos días.

Automatiza tus Reportes Diarios en Excel con Power Query (sin copiar y pegar)

Suscríbete para más tutoriales

🎁 Descarga el archivo para practicar

Escribe tu correo electrónico para recibir gratis el archivo para practicar.

*Al enviar mi correo electrónico, acepto recibir noticias y ofertas. Puedo darme de baja en cualquier momento.

1. Preparación de los datos diarios

Cada hoja de reporte se convierte en una tabla de Excel (Ctrl + T) antes de importarla a Power Query. Esta conversión facilita la detección de todas las tablas mediante la función Excel.CurrentWorkbook().

---

2. Limpieza y transformación en Power Query

• Eliminación de columnas irrelevantes: se descartan las columnas auxiliares vacías.
• Filtrado de filas: se excluyen subtotales y totales.
• Relleno hacia abajo: se completan las celdas vacías con valores coherentes (ID, XOC, Nombre).
• Conversión de espacios vacíos a null para permitir el uso correcto de “Rellenar hacia abajo”.

---

3. División de campos compuestos mediante Lenguaje M

La columna Grupo contiene tres datos concatenados (Id Agente, XOC y Nombre). El siguiente fragmento en M la transforma en tres columnas independientes:

let
    Origen = Excel.CurrentWorkbook(),
    #"Filas filtradas2" = Table.SelectRows(Origen, each [Name] <> "Consulta1"),
    #"Columnas quitadas" = Table.RemoveColumns(#"Filas filtradas2",{"Name"}),
    #"Se expandió Content" = Table.ExpandTableColumn(#"Columnas quitadas", "Content", {"Grupo", "Columna1", "Fecha", "Caller ANI", "Columna2", "Columna3", "Fila de ACD", "Hora#(lf)Contestada", "Duración De #(lf)Llamada (seg)", "Hora#(lf)de Termino", "Tiempo en la Fila#(lf)(seg.)", "Ofrecida", "Arrivada", "Contestada", "Abandonada por el Agente"}, {"Grupo", "Columna1", "Fecha", "Caller ANI", "Columna2", "Columna3", "Fila de ACD", "Hora#(lf)Contestada", "Duración De #(lf)Llamada (seg)", "Hora#(lf)de Termino", "Tiempo en la Fila#(lf)(seg.)", "Ofrecida", "Arrivada", "Contestada", "Abandonada por el Agente"}),
    #"Columnas quitadas1" = Table.RemoveColumns(#"Se expandió Content",{"Columna1", "Columna2", "Columna3"}),
    #"Filas filtradas" = Table.SelectRows(#"Columnas quitadas1", each ([Grupo] <> 1 and [Grupo] <> "Gran Total" and [Grupo] <> "Total x Agente" and [Grupo] <> "Total x Grupo")),
    #"Valor reemplazado" = Table.ReplaceValue(#"Filas filtradas","",null,Replacer.ReplaceValue,{"Grupo"}),
    #"Rellenar hacia abajo" = Table.FillDown(#"Valor reemplazado",{"Grupo"}),

    #"Columnas separadas" = Table.TransformColumns(#"Rellenar hacia abajo", {
        "Grupo", each 
            let 
                id = Text.BetweenDelimiters(_, "Id Agente: ", " Clave:"),
                clave = Text.BetweenDelimiters(_, "Clave: ", " Nombre:"),
                nombre = Text.AfterDelimiter(_, "Nombre: ")
            in 
                [IdAgente = id, Clave = clave, Nombre = nombre]
    , type record}),
    #"Expandido Grupo" = Table.ExpandRecordColumn(#"Columnas separadas", "Grupo", {"IdAgente", "Clave", "Nombre"}),
    #"Filas filtradas1" = Table.SelectRows(#"Expandido Grupo", each [Caller ANI] <> null)

in
    #"Filas filtradas1"

Inserte este bloque inmediatamente después del paso “Rellenar hacia abajo” y antes de la instrucción in para obtener las tres columnas limpias.

---

4. Consolidación de múltiples tablas

1. Consulta en blanco mCopyEdit= Excel.CurrentWorkbook()
2. Se filtra la lista de objetos para incluir únicamente las tablas diarias y excluir consultas intermedias.
3. Se expanden los contenidos y se repite la secuencia de limpieza descrita anteriormente.

Tras cargar el resultado como tabla, la consulta incorporará automáticamente cualquier hoja nueva añadida con la misma estructura.

---

5. Ventajas operativas

Aspecto	Beneficio
Escalabilidad	Nuevos reportes se integran sin editar consultas.
Consistencia	Formateo uniforme de todos los días.
Productividad	Eliminación de tareas manuales repetitivas.
Trazabilidad	Pasos de transformación documentados y replicables.

---

6. Profundizar en Lenguaje M

El Lenguaje M ofrece un control de transformación más granular que la interfaz gráfica de Power Query. Dominarlo permite:

• Generar pasos complejos en una única instrucción.
• Automatizar lógicas condicionales avanzadas.
• Reducir la dependencia de macros VBA para tareas ETL.

Para quienes deseen profundizar, el curso completo de M en Deztaca incluye ejercicios prácticos sobre temas como:

• Uso de la palabra clave each.
• Manipulación de texto y columnas dinámicas.
• Secuenciación de filas y tablas contiguas.
• Creación de flujos de datos en Power BI Service.

Más información: https://www.deztaca.com/m

Certifícate en Excel

Tienes experiencia en Excel y quieres mejorar tus oportunidades laborales? Obtén la Certificación Excel Expert avalada por Microsoft.