martes, 26 de abril de 2022

Power Query: List.RemoveItems o cómo elegir columnas

Un problema habitual al trabajar con Power Query es trabajar con una lista de columnas de forma controlada, y es que a veces operar de forma dinámica sobre una lista de columnas facilita el trabajo presente y futuro...
Veamos un par de ejemplos...
Power Query: List.RemoveItems o cómo elegir columnas


Propongo dos ejemplos a desarrollar, cada uno a partir de una de las tablas de la imagen.
Ejercicio primero: Obtener de entre los valores de cada país (sin considerar el resto de columnas: Total, Producto o Comercial) el valor mínimo
Ejercicio segundo: Recuperar el primer valor que encuentre no vacío entre los países.
Obviamente decir que nuestro número de países puede crecer en cualquier momento, y por tanto, nuestro proceso debe considerar tal situación!!

Ambos desarrollos se basan en la misma idea: Obtener una lista de nombres variable de países empleando la función M: List.RemoveItems
List.RemoveItems(list1 as list, list2 as list) as list
función que elimina/borra de la lista1 los elementos informados en la lista2, siempre que existan!

Empezaremos cargando ambos rangos con nombres definidos asignados (ndMIN y ndPRIMER) dentro del Editor de Power Query.

Desde el Editor de Power Query accederemos al Editor Avanzado de la consulta asociada a 'ndMIN' y escribiremos:
let
    Origen = Excel.CurrentWorkbook(){[Name="ndMIN"]}[Content],
    EncabezadosPromovidos = Table.PromoteHeaders(Origen, [PromoteAllScalars=true]),
    TipoCambiado = Table.TransformColumnTypes(EncabezadosPromovidos,{{"Producto", type text}, {"Comercial", type text}, {"ES", Int64.Type}, {"FR", Int64.Type}, {"IT", Int64.Type}, {"PT", Int64.Type}, {"Total", Int64.Type}}),

    //Obtenemos la lista de columnas sobre las que trabajar
    //List.RemoveItems nos permite operar con columnas por exclusión de las no deseadas
    lstPaises=List.Buffer (List.RemoveItems (Table.ColumnNames(TipoCambiado), {"Producto", "Comercial","Total"})),

    //Para obtener el valor mínimo entre esas columnas, operamos por ejemplo a partir de Records
    AddCol_MIN=Table.AddColumn(TipoCambiado,"MIN", each 
            List.Min(
                List.RemoveNulls(
                    Record.ToList(
                        Record.SelectFields(_, lstPaises))))),
    
    //Acabamos devolviendo nuestra nueva columna con los tres campos principales de la tabla: Producto, Comercial y Total
    Final=Table.SelectColumns(AddCol_MIN, List.Difference(Table.ColumnNames(AddCol_MIN), lstPaises))

in
    Final

Acabaríamos devolviendo nuestro resultado a la hoja de cálculo.

El segundo ejemplo sería por tanto prácticamente idéntico, únicamente modificando la función M de List.Min por List.First.
let
    Origen = Excel.CurrentWorkbook(){[Name="ndPRIMER"]}[Content],
    EncabezadosPromovidos = Table.PromoteHeaders(Origen, [PromoteAllScalars=true]),
    TipoCambiado = Table.TransformColumnTypes(EncabezadosPromovidos,{{"Producto", type text}, {"Comercial", type text}, {"ES", Int64.Type}, {"FR", Int64.Type}, {"IT", Int64.Type}, {"PT", Int64.Type}, {"Total", Int64.Type}}),

    //Obtenemos la lista de columnas sobre las que trabajar
    //List.RemoveItems nos permite operar con columnas por exclusión de las no deseadas
    lstPaises=List.Buffer (List.RemoveItems (Table.ColumnNames(TipoCambiado), {"Producto", "Comercial","Total"})),

    //Para obtener el primer valor no nulo entre esas columnas, operamos por ejemplo a partir de Records
    AddCol_PRIMER=Table.AddColumn(TipoCambiado,"PRIMER", each 
            List.First(
                List.RemoveNulls(
                    Record.ToList(
                        Record.SelectFields(_, lstPaises))))),
    
    //Acabamos devolviendo nuestra nueva columna con los tres campos principales de la tabla: Producto, Comercial y Total
    Final=Table.SelectColumns(AddCol_PRIMER, List.Difference(Table.ColumnNames(AddCol_PRIMER), lstPaises))

in
    Final

Igualmente devolveríamos el resultado a la hoja de cálculo...
Power Query: List.RemoveItems o cómo elegir columnas


Estas acciones nos permitirán en el futuro añadir nuevos países en nuestros rangos y que éstos se tengan en cuenta en nuestros cálculos sin necesidad de tocar nuestro código M.

Resaltar un par de funciones...
Por supuesto la comentada List.RemoveItems que por exclusión permite obtener listas variables.
Y también la función M List.Difference con la que conseguimos una nueva lista de las columnas que no coincidan con la de países previamente obtenidas.
ten en cuenta que
List.Difference(list1 as list, list2 as list, optional equationCriteria as any) as list
nos devolverá los elementos de la lista list1 que no están en la lista list2, en nuestro ejemplo, de la totalidad de columnas trabajadas, incluyendo la nueva calculada, aquellas que no coincidad con la lista de países.

Funciones de tipo Lista que nos ayudarán a dinamizar nuestros trabajos...

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.