Forum Discussion
Solved
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...
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"
Hi pelowski ,
We can meet your requirement by following steps in Power Query Editor:
1. group all the rows by Group Column.
2. add a custom using following code:
let maxcount = Value.As(Table.RowCount([Data]),Int64.Type),
d = [Data]
in
if maxcount <= 2
then #table({"Items","Count"},{{Text.Combine(Table.ToList(Table.SelectColumns([Data],{"Item"})),","),maxcount}})
else
let NumberList = List.Generate(()=>3,each _ <= maxcount,each _+1),
NumberTable = Table.FromList(NumberList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
StartOrderList = Table.AddColumn(NumberTable,"Column2",each let n1 = Value.As([Column1],Int64.Type) in List.Generate(()=>1,each _+n1-1<=maxcount,each _+1)),
StartOrderTable = Table.ExpandTableColumn(Table.AddColumn(StartOrderList,"StartOrder", each Table.FromList([Column2], Splitter.SplitByNothing(), null, null, ExtraValues.Error)),"StartOrder", {"Column1"}, {"StartOrder"}),
ItemsTable = Table.AddColumn(StartOrderTable,"ItemsTable",each Table.SelectRows(d,let so = [StartOrder],c1 = [Column1] in each [Order]>=so and [Order] <= so+c1-1)),
FinalTable = Table.RenameColumns(Table.SelectColumns(Table.AddColumn(ItemsTable,"Items",each Text.Combine(Table.ToList(Table.SelectColumns([ItemsTable],{"Item"})),",")),{"Column1","Items"}),{"Column1","Count"})
in Table.ReorderColumns(FinalTable,{"Items","Count"})
3. Expand the new custom column
4. rename and remove unsessary Data Column, or you can also expand another columns you want from the data Column.
5. Select the count number you want
All the queies are here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNyVNJR8k1Mz0wG0oZKsTowMY/UxBIgZYQkFJCYnFpUDGQYIwkG5Sdnp5aARE0gom4+QLZzRmZqWjHCSLAgUH82xAAjJFGfzPw8JEOhKkuKMvMxTA3LzM7MSweJmiKJOqUmgg01g4h5gA0oSk1JLMkvQnICWNypqDQzD+qCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Grouping = _t, Item = _t, Order = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Grouping", type text}, {"Item", type text}, {"Order", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Grouping"}, {{"Data", each _, type table [Grouping=text, Item=text, Order=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Items", each let maxcount = Value.As(Table.RowCount([Data]),Int64.Type),
d = [Data]
in
if maxcount <= 2
then #table({"Items","Count"},{{Text.Combine(Table.ToList(Table.SelectColumns([Data],{"Item"})),","),maxcount}})
else
let NumberList = List.Generate(()=>3,each _ <= maxcount,each _+1),
NumberTable = Table.FromList(NumberList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
StartOrderList = Table.AddColumn(NumberTable,"Column2",each let n1 = Value.As([Column1],Int64.Type) in List.Generate(()=>1,each _+n1-1<=maxcount,each _+1)),
StartOrderTable = Table.ExpandTableColumn(Table.AddColumn(StartOrderList,"StartOrder", each Table.FromList([Column2], Splitter.SplitByNothing(), null, null, ExtraValues.Error)),"StartOrder", {"Column1"}, {"StartOrder"}),
ItemsTable = Table.AddColumn(StartOrderTable,"ItemsTable",each Table.SelectRows(d,let so = [StartOrder],c1 = [Column1] in each [Order]>=so and [Order] <= so+c1-1)),
FinalTable = Table.RenameColumns(Table.SelectColumns(Table.AddColumn(ItemsTable,"Items",each Text.Combine(Table.ToList(Table.SelectColumns([ItemsTable],{"Item"})),",")),{"Column1","Items"}),{"Column1","Count"})
in Table.ReorderColumns(FinalTable,{"Items","Count"})),
#"Expanded Items" = Table.ExpandTableColumn(#"Added Custom", "Items", {"Items", "Count"}, {"Items.Items", "Items.Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Items",{{"Items.Items", "Items"}, {"Items.Count", "Count"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Data"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Count] <> 5 and [Count] <> 6))
in
#"Filtered Rows"
Best regards,
Wow, thank you! It's going to take a little while to wrap my head around this solution. Thank you for the help!
