Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 sample spreadsheet with this data can be found here.
Things to Note
What I've Tried Already
I've tried various permutations of the following...
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!
Solved! Go to 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"
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!
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.