Aprenderemos hoy una función M de Power Query MUY interesante, enfocada a aplicar filtros sobre filas de manera formulada. Hablamos de:
Table.SelectRows(table as table, condition as function) as table
esta función nos devolverá una tabla con aquellas filas que coincidan con nuestras condiciones de filtro...
Para mostrar un ejemplo algo más avanzado de esta función aplicaremos un ejemplo que replica nuestra función SUMAR.SI.CONJUNTO de la hoja de cálculo.
Nuestro punto de partida es:
1-la tabla 'TblDATOS',
2- un rango con un nombre definido asignado: 'ndRESUMEN' correspondiente al rango G2:H5
3- una celda con el año insertado, con nombre asignado 'ndAño' (celda I1).
Nuestro objetivo es, empleando Power Query (y la función Table.SelectRows en concreto), llegar a obtener el acumulado por PAÍS, PRODUCTO y AÑO detallado en G2:H5.
Así pues, cargaremos el rango 'ndRESUMEN' y la 'TblDATOS' al editor de consultas de Power Query.
Desde la ficha Datos > grupo Obtener y transformar > Desde Tabla o rango
Inicialmente cargaremos ambas tablas 'Solo como conexión'.
Ya en el editor abriremos el Editor avanzado del rango 'ndRESUMEN':
let
Origen = Excel.CurrentWorkbook(){[Name="ndRESUMEN"]}[Content],
pAño = Excel.CurrentWorkbook(){[Name="ndAño"]}[Content][Column1]{0},
EncabezadosPromovidos = Table.PromoteHeaders(Origen, [PromoteAllScalars=true]),
AddCol_SUMAR_SI_CONJUNTO = Table.AddColumn(EncabezadosPromovidos, "Acum", each List.Sum(
Table.SelectRows(
TblDATOS,
(sumar_si) => sumar_si[País]=[Paises] and
sumar_si[Producto]=[Pdto] and
sumar_si[Año]=pAño)[Unidades])),
OtrasColumnasQuitadas = Table.SelectColumns(AddCol_SUMAR_SI_CONJUNTO,{"Acum"})
in
OtrasColumnasQuitadas
Donde cada paso implicaría lo siguiente...
El primer paso carga el rango 'ndRESUMEN'. Esto lo habría hecho el asistente al cargar el rango...
Origen = Excel.CurrentWorkbook(){[Name="ndRESUMEN"]}[Content],
En el segundo paso recuperamos el dato de la celda 'ndAño' que trataremos como un parámetro dentro de la consulta...
pAño = Excel.CurrentWorkbook(){[Name="ndAño"]}[Content][Column1]{0},
En el tercer paso promovemos la primera fila como encabezados (muy normal en los casos que cargamos rangos...)
EncabezadosPromovidos = Table.PromoteHeaders(Origen, [PromoteAllScalars=true]),
fijate que operamos sobre la primera línea 'Origen' !!
En el siguiente paso está la 'chicha' del ejercicio!!:
AddCol_SUMAR_SI_CONJUNTO = Table.AddColumn(EncabezadosPromovidos, "Acum", each List.Sum(
Table.SelectRows(
TblDATOS,
(sumar_si) => sumar_si[País]=[Paises] and
sumar_si[Producto]=[Pdto] and
sumar_si[Año]=pAño)[Unidades])),
Añadimos con este paso una nueva columna llamada 'Acum' resultante de Acumular/Sumar (List.Sum) los elementos de la lista resultantes de aplicar ciertas condiciones a la tabla 'TblDATOS'... condiciones, conseguidas en este caso, con la aplicación de una función personalizada:
(sumar_si) => sumar_si[País]=[Paises] and
sumar_si[Producto]=[Pdto] and
sumar_si[Año]=pAño)
Esta función aplica el triple criterio por País, Producto y Año.
Antes de aplicar List.Sum veríamos, tras insertar nuestra función Table.SelectRows:
donde observamos que para cada fila de 'ndRESUMEN' tenemos una Tabla solo con las filas de 'TblDATOS' que cumplen la triple condición...
Table.SelectRows(
TblDATOS,
(sumar_si) => sumar_si[País]=[Paises] and
sumar_si[Producto]=[Pdto] and
sumar_si[Año]=pAño)
Como de la 'TblDATOS' solo nos interesa el campo 'Unidades' para acumular esas cantidades, al código anterior le añadiremos dicho campo:
Table.SelectRows(
TblDATOS,
(sumar_si) => sumar_si[País]=[Paises] and
sumar_si[Producto]=[Pdto] and
sumar_si[Año]=pAño)[Unidades]
Lo que generará una Lista solo con los datos de la columna 'Unidades' y obviamente de los registros de la 'TblDATOS' que verificaran las tres condiciones...
Sobre esa lista de unidades aplicamos List.Sum para obtener el acumulado buscado!!... como veíamos en el código completo.
Termino en este caso mostrando solo la columna creada 'Acum' (con nuestro cálculo), y lo mostramos en la hoja de cálculo...
Tendremos que cambiar el tipo de carga de 'Solo conexión' a 'Tabla' en la hoja de cálculo.
Llegando al resultado buscado...
Especialmente interesante es el uso dentro de Table.SelectRows como argumento de condiciones de la función personalizada, que evita tener que expandir los campos de la TblDATOS para aplica posteriormente condiciones sobre la columnas (como hicimos en uno de los casos de este post); ganando en eficiencia y rapidez.
