Recientemente hablaba con un cliente de la posibilidad de añadir subtotales dentro de una consulta de Power Query...
Es sabido por todos que no es una opción desde la herramienta de Subtotales (al margen de lo poco práctico para el trabajo)... pero la cuestión quedó en el aire y me puse a investigar y probar, y aquí estamos.
Increible!!... con algunos pasos y funciones M de una consulta en el editor de Power Query, sí es posible añadir subtotales!!!.
¿Que funciones M debemos conocer?:
1. Table.SelectRows(table as table, condition as function)
que nos devuelve una tabla de filas de table, que coincide con la selección condition; o sea, aplicar un filtro con una condición o varias.
2. Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function)
muchos argumentos para esta función que nos permite agrupar.. pero muchos opcionales que no suelen emplearse.
La conoceras por que frecuentemente llegamos a ella desde el botón de Agrupar por
3. Table.Combine(tables as list, optional columns as any)
Sirve para Anexar tablas (una debajo por otra, verticalmente).
4. Última relevante, al margen de otras que también emplearemos: #table(columns as any, rows as any)
Crea un valor de tabla a partir de las columnas y filas donde cada elemento de la lista es una lista interna que contiene los valores de columna de una sola fila.
El argumento columns puede ser una lista de nombres de columna, un tipo de tabla, un número de columnas o NULL.
MUY importante esta función, ya que es ésta la que va a añadir esos subtotales!!
Comencemos...
Partimos de la tabla de regiones de Europa, paises, zonas, comerciales, unidades de nuestra hoja de cálculo. Tabla que cargaremos al editor de consultas de Power Query como siempre (desde la ficha Datos > grupo Obtener y transformar datos > botón Desde tabla o rango)
Con la tabla cargada, y ya en el editor, accedemos al Editor Avanzado para incluir el siguiente código M:
Explicamos paso a paso...
El primer paso:
ha cargado la tabla a la consulta...
El segundo paso:
Aquí está la clave.. OJO!!!.
Agrupamos por País, añadiendo un campo 'temporal', donde en lugar de realizar alguna acumulación u operación agregada, añade o anexa al detalle de elementos correspondientes a cada 'País' una 'nueva tabla' de dos columnas 'País' y 'UDS' con un solo registro:
Para 'País': Subtotal y el nombre del País agrupado (por ejemplo, 'Subtotal ES')
Para 'UDS': la Suma Acumulada de dicho País
Algo como esto...
Sobre el resultado de esa tabla 'temporal' aplicamos un filtro, con Table.SelectRows con un filtro aplicado sobre la columna 'País' para descartar valores vacíos o nulos (each [País] <> null and [País] <> "")
El tercer paso es simple:
Aquí anexamos cada tabla obtenida, de cada País, con el paso anterior, una a continuación de la otra.
En los dos siguientes pasos:
Donde empleando los asistentes hemos duplicado la columna 'Europa' y renombrado como 'Europa_', y a continuación hemos Rellenado hacía abajo (clic derecho sobre el nuevo campo, opción Rellenar > Abajo), con el siguiente resultado.
Este paso, donde añadimos una nueva columna de 'Europa' es fundamental para poder añadir el siguiente Subtotal!!
Vamos al siguiente paso, donde al igual que hicimos para 'País', replicaremos el mismo proceso:
Observa que los pasos son idénticos, excepto en elemento añadido en el campo 'UDS' de la tabla 'temporal_2' que hemos forzado la Suma Acumulada de las 'UDS' de la región de 'Europa_' dividida entre dos para evitar el subtotal anterior por País !!.
Para cada Región de 'Europa_' tendremos los registros de dicha región y al final el añadido con '#table:
Para 'Europa_': Total y el nombre de la región de Europa_ agrupado (por ejemplo, 'Total CENTRO')
Para 'UDS': la Suma Acumulada de dicha región divida por dos (List.Sum([UDS])/2)
NO OLVIDES que hemos aplicado un filtro sobre lo anterior para descartar los valores vacíos o nulos!!!
Algo como esto...
El siguiente paso obvio, es Anexar esas regiones con sus subtotales:
Último paso, eliminamos la columna 'Europa_' que ya no necesitamos:
LLegando al resultado esperado!!.
Ya podemos Cerrar y Cargar en... sobre la hoja de cálculo.
Por estética final, y ya en la tabla de la hoja de cálculo, aplicamos un par de reglas de formato condicional sobre los campos Europa y País para dar 'algo de color' a las filas de los parciales:
Y para rematar, incorporamos a la Tabla de la hoja de cálculo la Fila de totales, personalizando la fórmula del campo 'UDS' con:
=SUMAR.SI([Europa];"Total*";[UDS])
Increible trabajo, y laborioso... ;-)
Es sabido por todos que no es una opción desde la herramienta de Subtotales (al margen de lo poco práctico para el trabajo)... pero la cuestión quedó en el aire y me puse a investigar y probar, y aquí estamos.
Increible!!... con algunos pasos y funciones M de una consulta en el editor de Power Query, sí es posible añadir subtotales!!!.
¿Que funciones M debemos conocer?:
1. Table.SelectRows(table as table, condition as function)
que nos devuelve una tabla de filas de table, que coincide con la selección condition; o sea, aplicar un filtro con una condición o varias.
2. Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function)
muchos argumentos para esta función que nos permite agrupar.. pero muchos opcionales que no suelen emplearse.
La conoceras por que frecuentemente llegamos a ella desde el botón de Agrupar por
3. Table.Combine(tables as list, optional columns as any)
Sirve para Anexar tablas (una debajo por otra, verticalmente).
4. Última relevante, al margen de otras que también emplearemos: #table(columns as any, rows as any)
Crea un valor de tabla a partir de las columnas y filas donde cada elemento de la lista es una lista interna que contiene los valores de columna de una sola fila.
El argumento columns puede ser una lista de nombres de columna, un tipo de tabla, un número de columnas o NULL.
MUY importante esta función, ya que es ésta la que va a añadir esos subtotales!!
Comencemos...
Partimos de la tabla de regiones de Europa, paises, zonas, comerciales, unidades de nuestra hoja de cálculo. Tabla que cargaremos al editor de consultas de Power Query como siempre (desde la ficha Datos > grupo Obtener y transformar datos > botón Desde tabla o rango)
Con la tabla cargada, y ya en el editor, accedemos al Editor Avanzado para incluir el siguiente código M:
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
//
AgrupaPAIS =
Table.SelectRows(
Table.Group(Origen, {"País"}, {"temporal", each _
& #table(
{"País", "UDS"},
{{
"Subtotal " & [País]{0},
List.Sum([UDS])
}}
)}),
each [País] <> null and [País] <> ""),
CombinacionPAIS = Table.Combine(AgrupaPAIS[temporal]),
//
DuplicadaEU = Table.DuplicateColumn(CombinacionPAIS, "Europa", "Europa_"),
RellenaEU = Table.FillDown(DuplicadaEU,{"Europa_"}),
//
AgrupaEU =
Table.SelectRows(
Table.Group(RellenaEU, {"Europa_"}, {"temporal_2", each _
& #table(
{"Europa", "UDS"},
{{
"Total " & [Europa_]{0} ,
List.Sum([UDS])/2
}}
)}),
each [Europa_] <> null and [Europa_] <> ""),
//
CombinacionEU = Table.Combine(AgrupaEU[temporal_2]),
ColumnasQuitadas = Table.RemoveColumns(CombinacionEU,{"Europa_"})
in
ColumnasQuitadas
El primer paso:
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content], ha cargado la tabla a la consulta...
El segundo paso:
AgrupaPAIS =
Table.SelectRows(
Table.Group(Origen, {"País"}, {"temporal", each _
& #table(
{"País", "UDS"},
{{
"Subtotal " & [País]{0},
List.Sum([UDS])
}}
)}),
each [País] <> null and [País] <> ""),Aquí está la clave.. OJO!!!.
Agrupamos por País, añadiendo un campo 'temporal', donde en lugar de realizar alguna acumulación u operación agregada, añade o anexa al detalle de elementos correspondientes a cada 'País' una 'nueva tabla' de dos columnas 'País' y 'UDS' con un solo registro:
Para 'País': Subtotal y el nombre del País agrupado (por ejemplo, 'Subtotal ES')
Para 'UDS': la Suma Acumulada de dicho País
Algo como esto...
Sobre el resultado de esa tabla 'temporal' aplicamos un filtro, con Table.SelectRows con un filtro aplicado sobre la columna 'País' para descartar valores vacíos o nulos (each [País] <> null and [País] <> "")
El tercer paso es simple:
CombinacionPAIS = Table.Combine(AgrupaPAIS[temporal]),
Aquí anexamos cada tabla obtenida, de cada País, con el paso anterior, una a continuación de la otra.
En los dos siguientes pasos:
DuplicadaEU = Table.DuplicateColumn(CombinacionPAIS, "Europa", "Europa_"),
RellenaEU = Table.FillDown(DuplicadaEU,{"Europa_"}),Donde empleando los asistentes hemos duplicado la columna 'Europa' y renombrado como 'Europa_', y a continuación hemos Rellenado hacía abajo (clic derecho sobre el nuevo campo, opción Rellenar > Abajo), con el siguiente resultado.
Este paso, donde añadimos una nueva columna de 'Europa' es fundamental para poder añadir el siguiente Subtotal!!
Vamos al siguiente paso, donde al igual que hicimos para 'País', replicaremos el mismo proceso:
AgrupaEU =
Table.SelectRows(
Table.Group(RellenaEU, {"Europa_"}, {"temporal_2", each _
& #table(
{"Europa", "UDS"},
{{
"Total " & [Europa_]{0} ,
List.Sum([UDS])/2
}}
)}),
each [Europa_] <> null and [Europa_] <> ""),Observa que los pasos son idénticos, excepto en elemento añadido en el campo 'UDS' de la tabla 'temporal_2' que hemos forzado la Suma Acumulada de las 'UDS' de la región de 'Europa_' dividida entre dos para evitar el subtotal anterior por País !!.
Para cada Región de 'Europa_' tendremos los registros de dicha región y al final el añadido con '#table:
Para 'Europa_': Total y el nombre de la región de Europa_ agrupado (por ejemplo, 'Total CENTRO')
Para 'UDS': la Suma Acumulada de dicha región divida por dos (List.Sum([UDS])/2)
NO OLVIDES que hemos aplicado un filtro sobre lo anterior para descartar los valores vacíos o nulos!!!
Algo como esto...
El siguiente paso obvio, es Anexar esas regiones con sus subtotales:
CombinacionEU = Table.Combine(AgrupaEU[temporal_2]),
Último paso, eliminamos la columna 'Europa_' que ya no necesitamos:
ColumnasQuitadas = Table.RemoveColumns(CombinacionEU,{"Europa_"})LLegando al resultado esperado!!.
Ya podemos Cerrar y Cargar en... sobre la hoja de cálculo.
Por estética final, y ya en la tabla de la hoja de cálculo, aplicamos un par de reglas de formato condicional sobre los campos Europa y País para dar 'algo de color' a las filas de los parciales:
Y para rematar, incorporamos a la Tabla de la hoja de cálculo la Fila de totales, personalizando la fórmula del campo 'UDS' con:
=SUMAR.SI([Europa];"Total*";[UDS])
Increible trabajo, y laborioso... ;-)
