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...
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:
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.
Igualmente devolveríamos el resultado a la hoja de cálculo...
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...
Veamos un par de ejemplos...
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...
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...
This post first appeared on EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDA, please read the originial post: here
