Forum Discussion

pelowski's avatar
pelowski
Helper III
6 years ago
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...
  • Anonymous's avatar
    Anonymous
    6 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"