Forum Discussion
Power M Query custom aggregate function to return most common value within a group
Here is the answer I came up with. I have never written a Power M Query function definition so all feedback on style and performance welcome.
I think it is a useful function for when your data is mostly correct but you need to take the common value.
The logic of the function is:
- get the unique values and convert to table
- find number of times each occurs in the input
- Sort by descending and return the string in the first row - the most commonly occuring string
I could add something for how nulls are handled (eg ignore null if top string and return next one down) but this seems to work for me
Function is MostCommon (you have to name the query)
let fnMostCommon = (ListIn) => let uniquevalues=List.Distinct(ListIn), result=Table.FromList(uniquevalues,null,{"u"}), result2=Table.AddColumn(result ,"freq", each List.Count(List.PositionOf(ListIn, [u], 100))), result3 = Table.Sort(result2,{"freq", Order.Descending}), result4 = List.First(result3[u]) in result4 in fnMostCommonAnd the test harness is:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQozhBCxOlC+ETa+EZifhKY+CVl9LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [G1 = _t, A1 = _t, A2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"G1", type text}, {"A1", type text}, {"A2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"G1"}, {{"Agg", each MostCommon([A1]), type text}, {"Agg2", each AllConcat([A1]), type text}}), a = #"Grouped Rows"{[G1="a"]}[Agg] in a
Hi Anonymous ,
I created a sample. Maybe it helps a little. Please have a try.
- Duplicate orginal table as Table(2) and group by it.
- Group by columns of Table.
- Merge two tables and append the new column
- Remove "Count" column and rows.
Table:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQozhBCxOlC+ETa+EZifhKY+CVl9LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [G1 = _t, A1 = _t, A2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"G1", type text}, {"A1", type text}, {"A2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"G1", "A1"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"G1"}, #"Table (2)", {"G1"}, "Table (2)", JoinKind.LeftOuter),
#"Aggregated Table (2)" = Table.AggregateTableColumn(#"Merged Queries", "Table (2)", {{"Count", List.Min, "Min of Table (2).Count"}}),
#"Removed Columns" = Table.RemoveColumns(#"Aggregated Table (2)",{"Count"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",1)
in
#"Removed Bottom Rows"Table(2):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQozhBCxOlC+ETa+EZifhKY+CVl9LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [G1 = _t, A1 = _t, A2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"G1", type text}, {"A1", type text}, {"A2", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"G1", "A1"}, {{"Count", each List.Min([A2]), type text}})
in
#"Grouped Rows"Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.
Thank you Xue Ding. of course that is how I should have presented the problem - with code using Table.FromRows!
My apologies - I should have said that I been using the technique of creating a new table and merging it back in.
Based on a post in stack overflow: Select row with MAX value per category Power BI
I wanted to create a new aggregate function because:
1 - it will be easy to read on the code. It will just appear with the standard aggregate functions like MAX, SUM etc
2 - To see if it ran any faster
3 - it will reduce the number of queries, for clarity
Writing the question yesterday has helped me think about the problem and I will write something based on your test data Mike
Here is the answer I came up with. I have never written a Power M Query function definition so all feedback on style and performance welcome.
I think it is a useful function for when your data is mostly correct but you need to take the common value.
The logic of the function is:
- get the unique values and convert to table
- find number of times each occurs in the input
- Sort by descending and return the string in the first row - the most commonly occuring string
I could add something for how nulls are handled (eg ignore null if top string and return next one down) but this seems to work for me
Function is MostCommon (you have to name the query)
let fnMostCommon = (ListIn) => let uniquevalues=List.Distinct(ListIn), result=Table.FromList(uniquevalues,null,{"u"}), result2=Table.AddColumn(result ,"freq", each List.Count(List.PositionOf(ListIn, [u], 100))), result3 = Table.Sort(result2,{"freq", Order.Descending}), result4 = List.First(result3[u]) in result4 in fnMostCommonAnd the test harness is:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUQozhBCxOlC+ETa+EZifhKY+CVl9LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [G1 = _t, A1 = _t, A2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"G1", type text}, {"A1", type text}, {"A2", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"G1"}, {{"Agg", each MostCommon([A1]), type text}, {"Agg2", each AllConcat([A1]), type text}}), a = #"Grouped Rows"{[G1="a"]}[Agg] in a