Forum Discussion
gracitg8
2 years agoNew Member
Create a custom column in Powery Query filtering about another column
Hello everyone! I have a problem in power query that I don't know how to translate into M code, I hope someone can help me with it. I have the following table, where the month and category are def...
- 2 years ago
Table.Group is useful here. Add the column after you have grouped by month; then re-expand the table:
let //My source is an Excel table but can be anything Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"mes", Int64.Type}, {"Categoría", type text}}), //create list of all the categories #"All Categoría" = List.Distinct(#"Changed Type"[Categoría]), //group by "mes", then add the column in a custom aggregation #"Grouped Rows" = Table.Group(#"Changed Type", {"mes"}, { {"All Cat present", (t)=>Table.AddColumn(t,"Resultado", each if List.ContainsAll(t[Categoría], #"All Categoría") then t[mes]{0} else null, Int64.Type), type table [mes=nullable number, Categoría=nullable text, Resultado=Int64.Type]}}), //Re-expand the collapsed table #"Expanded All Cat present" = Table.ExpandTableColumn(#"Grouped Rows", "All Cat present", {"Categoría", "Resultado"}) in #"Expanded All Cat present"Data
Results
ronrsnfld
2 years agoSuper User
Table.Group is useful here. Add the column after you have grouped by month; then re-expand the table:
let
//My source is an Excel table but can be anything
Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"mes", Int64.Type}, {"Categoría", type text}}),
//create list of all the categories
#"All Categoría" = List.Distinct(#"Changed Type"[Categoría]),
//group by "mes", then add the column in a custom aggregation
#"Grouped Rows" = Table.Group(#"Changed Type", {"mes"}, {
{"All Cat present", (t)=>Table.AddColumn(t,"Resultado",
each if List.ContainsAll(t[Categoría], #"All Categoría") then t[mes]{0} else null, Int64.Type),
type table [mes=nullable number, Categoría=nullable text, Resultado=Int64.Type]}}),
//Re-expand the collapsed table
#"Expanded All Cat present" = Table.ExpandTableColumn(#"Grouped Rows", "All Cat present", {"Categoría", "Resultado"})
in
#"Expanded All Cat present"Data
Results