Combinations of Multiple Items using Power Query or DAX

[pelowski]

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.

Item Groupings I wish to achieve

A sample spreadsheet with this data can be found here.

Things to Note

1. The order is important
2. The number of items in a group might need to change.
3. Item groupings may consist of multiple groupings such as 3 and 4, or 4 and 5 and 6 item groupings.
4. If the total items in a list is less than the grouping size, I'd like to return all of them.

What I've Tried Already

I've tried various permutations of the following...

1. Table.Group and Text.Combine(List.Transform()) in Power Query
2. Calculate with a ConcatenateX in DAX

But I can't figure out how to iterate through a list to grab each N item grouping and append them together.  I also tried various searches in this regard but didn't see anything that fit this sort of grouping.

Any help that can be provided would be greatly appreciated!

[v-lid-msft]

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,

[pelowski]

Wow, thank you!  It's going to take a little while to wrap my head around this solution.  Thank you for the help!

[pelowski]

I guess I should also mention the "Why?"  What I want to do is look for trends.  I want to take these groupings and then throw them into a Matrix report to see if any common groupings are present.  So in a much larger dataset I want to see how many "x, y, z" groupings there are and if they're more or less than "a, b, c" groupings, etc.  In this case it's a series of actions taken by an anonymized person and I want to see if there are commonalities between people so we can help them be more successful with what they're ultimately trying to do.

[Anonymous · Accepted solution]

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]

@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!