Forum Discussion
pelowski
6 years agoHelper III
Combinations of Multiple Items using Power Query or DAX
I have an interesting challenge that I have not been able to figure out. Using either Power Query or DAX (either is fine) I'd like to create dynamic concatenated groupings of items. A samp...
- Anonymous6 years ago
This challenge is a good one. Below is M code that will meet it. You can modify the AddGroupSizes step to change the number of groupings that is currently set to 3 and 4.
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Grouping", type text}, {"Item", type text}, {"Order", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Grouping"}, {{"DataGroupSize", each Table.RowCount(_), type number}, {"Data", each Table.AddIndexColumn(_,"Index",0,1), type table}}), AddGroupSizes = Table.AddColumn(#"Grouped Rows", "GroupSizes",each {3,4}), #"Expanded GroupSizes1" = Table.ExpandListColumn(AddGroupSizes, "GroupSizes"), AddActualGroupSize = Table.AddColumn(#"Expanded GroupSizes1", "GroupSize", each if [DataGroupSize] < [GroupSizes] then [DataGroupSize] else [GroupSizes]), #"Expanded Data" = Table.ExpandTableColumn(AddActualGroupSize, "Data", {"Index"}, {"Index"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Index] <= [GroupSize])), #"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Grouping"},#"Changed Type",{"Grouping"},"Data",JoinKind.Inner), AddItems = Table.AddColumn(#"Merged Queries", "Items", each List.Range([Data][Item],[Index], [GroupSize])), #"Filtered Rows1" = Table.SelectRows(AddItems, each List.Count([Items]) = [GroupSize] ), Transform = Table.TransformColumns(#"Filtered Rows1",{{"Items", each Text.Combine(_,","), type text}}), #"Removed Other Columns" = Table.SelectColumns(Transform,{"Grouping", "GroupSizes", "Items"}) in #"Removed Other Columns"
Anonymous
6 years agoNot applicable
This challenge is a good one. Below is M code that will meet it. You can modify the AddGroupSizes step to change the number of groupings that is currently set to 3 and 4.
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Grouping", type text}, {"Item", type text}, {"Order", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Grouping"}, {{"DataGroupSize", each Table.RowCount(_), type number},
{"Data", each Table.AddIndexColumn(_,"Index",0,1), type table}}),
AddGroupSizes = Table.AddColumn(#"Grouped Rows", "GroupSizes",each {3,4}),
#"Expanded GroupSizes1" = Table.ExpandListColumn(AddGroupSizes, "GroupSizes"),
AddActualGroupSize = Table.AddColumn(#"Expanded GroupSizes1", "GroupSize", each if [DataGroupSize] < [GroupSizes] then [DataGroupSize] else [GroupSizes]),
#"Expanded Data" = Table.ExpandTableColumn(AddActualGroupSize, "Data", {"Index"}, {"Index"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Index] <= [GroupSize])),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Grouping"},#"Changed Type",{"Grouping"},"Data",JoinKind.Inner),
AddItems = Table.AddColumn(#"Merged Queries", "Items", each List.Range([Data][Item],[Index], [GroupSize])),
#"Filtered Rows1" = Table.SelectRows(AddItems, each List.Count([Items]) = [GroupSize] ),
Transform = Table.TransformColumns(#"Filtered Rows1",{{"Items", each Text.Combine(_,","), type text}}),
#"Removed Other Columns" = Table.SelectColumns(Transform,{"Grouping", "GroupSizes", "Items"})
in
#"Removed Other Columns"
pelowski
6 years agoHelper III
Anonymous, I've got to give you the nod here because of the sheer elegance of this solution! Thank you very much! I will be implementing it today!