Power query grouping

Question

Can Power query do this? So I have a group of parent IDs. If the parent Ids are the same but the values from the corresponding attributes are different, I want PQ to let me know they can be grouped together. Here is the example. So Parent IDs 12345 are the same, and the values are different, I want the output to say SDSKU..Yes Then if the Parent IDs 333 are the same and values are the same, then that will not be a grouping and I want it to say NO. See image link

*"Can Power query do this? "* Yes, it can. – Ron Rosenfeld Aug 15 '22 at 20:47 — Ron Rosenfeld, Aug 15 '22 at 20:47

score 0 · Answer 1 · answered Aug 15 '22 at 21:03

If you mean by "values" the values of the column "Color", try the M code below :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent ID", Int64.Type}, {"Kitchen Sink", Int64.Type}, {"Color", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Parent ID", "Kitchen Sink"}, {{"AllData", each _, type table [Parent ID=nullable number, Kitchen Sink=nullable number, Color=nullable text]}, {"OccuID", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NumberOfColors", each List.Count(List.Distinct([AllData][Color]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "SDSKU", each if [OccuID] = [NumberOfColors] then "Yes" else "No"),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Added Custom1", "AllData", {"Kitchen Sink", "Color"}, {"Kitchen Sink.1", "Color"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded AllData",{"OccuID", "NumberOfColors"})
in
    #"Removed Columns"

horseyride · Answer 2 · 2022-08-16T13:17:43.337

If "attributes" are the value of every column except the one named Parent ID, try the M code below :

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source , {"Parent ID"}, {
    {"data", each _, type table },
    {"check", each if Table.RowCount(_) = Table.RowCount(Table.Distinct(_, List.Difference(Table.ColumnNames(_),{"Parent ID"}))) then "YES" else "NO"}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", List.Difference(Table.ColumnNames(Source),{"Parent ID"}), List.Difference(Table.ColumnNames(Source),{"Parent ID"}))
in #"Expanded data"

Power query grouping

2 Answers2