Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pelowski
Helper III
Helper 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.

Item Groupings I wish to achieveItem 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!

1 ACCEPTED SOLUTION
Anonymous
Not 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"

 

View solution in original post

5 REPLIES 5
v-lid-msft
Community Support
Community Support

Hi @pelowski ,

 

We can meet your requirement by following steps in Power Query Editor:

 

1. group all the rows by Group Column.

 

1.jpg

 

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"})

 

6.jpg

 

3. Expand the new custom column

 

3.jpg

 

4. rename and remove unsessary Data Column, or you can also expand another columns you want from the data Column.

 

4.jpg5.jpg7.jpg

 

5. Select the count number you want

 

8.jpg

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

pelowski
Helper III
Helper III

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
Not 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"

 

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors