Forum Discussion

gracitg8's avatar
gracitg8
New Member
2 years ago
Solved

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 defined. I need the code to generate my Result table, where it has to return the value of the month if all the categories are presented and if not a null value. I have tried several formulas but nothing works for me, any idea how to get it?

thank you so much ! Greetings to all

ps: sorry for my categories in Spanish

Need Help
Tips and Tricks
  • ronrsnfld's avatar
    ronrsnfld
    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

     

     

1 Reply

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    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