topic Re: Grouping/Ranking of elements in Power Query

Grouping/Ranking of elements

bartek_pepper — Tue, 29 Oct 2024 11:01:29 GMT

Hi Team,

I have the following logic to handle and I am not sure how to do it.

In the following table, we have got letters and colors.

Let's say that letter is a group and whenever there is a brown color assigned to the letter regardless of others, take brown.

letter	color	new column
a	yellow	brown
a	brown	brown
a	brown	brown
a	brown	brown
b	yellow	brown
b	yellow	brown
b	brown	brown
c	yellow	yellow
c	yellow	yellow
d	brown	brown
d	yellow	brown
d	yellow	brown

Can anyone suggest something?

Thanks.

Bart

Re: Grouping/Ranking of elements

shafiz_p — Tue, 29 Oct 2024 11:30:52 GMT

Hi bartek_pepper Try below M code using Advance Editor option:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"letter", type text}, {"color", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"letter"}, {{"AllColors", each _, type table [letter=nullable text, color=nullable text, new column=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NewColumn", each if List.Contains([AllColors][color], "brown") then "brown" else [AllColors]{0}[color]), #"Expanded AllColors" = Table.ExpandTableColumn(#"Added Custom", "AllColors", {"color"}) in #"Expanded AllColors"

Here is the desired output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

Re: Grouping/Ranking of elements

MFelix — Tue, 29 Oct 2024 11:54:11 GMT

Hi bartek_pepper ,

Try the following:

Add a new step with the following custom function:

= (CUSTOM) => let source = (CUSTOM as text) => Text.From([letter]) , ColorSelection= Table.SelectRows( #"Changed Type",each [letter] = CUSTOM) in ColorSelection

Add a new column

= Table.AddColumn(#"Changed Type", "Custom", each Table.Group (Custom1([letter]), {"letter"}, {"New color", each if List.Contains (_[color] , "brown") = true then "brown" else null} ))

Expand the column
Replace the null by the color

= Table.ReplaceValue(#"Expanded Custom",null,each [color],Replacer.ReplaceValue,{"New color"})

Full code below

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUapMzcnJL1eK1YFwk4ryy/MI8pJQNWLhItQmo0qicVNQ1KZgSsK4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [letter = _t, color = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"letter", type text}, {"color", type text}}), Custom1 = (CUSTOM) => let source = (CUSTOM as text) => Text.From([letter]) , ColorSelection= Table.SelectRows( #"Changed Type",each [letter] = CUSTOM) in ColorSelection, #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Group (Custom1([letter]), {"letter"}, {"New color", each if List.Contains (_[color] , "brown") = true then "brown" else null} )), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"New color"}, {"New color"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",null,each [color],Replacer.ReplaceValue,{"New color"}) in #"Replaced Value"

Re: Grouping/Ranking of elements

AlienSx — Tue, 29 Oct 2024 12:46:35 GMT

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], brown = Function.Invoke( Record.FromList, List.Reverse( Table.ToColumns( Table.Distinct( Table.SelectRows(Source, (x) => x[color] = "brown"), "letter" ) ) ) ), new_column = Table.AddColumn(Source, "new column", (x) => Record.FieldOrDefault(brown, x[letter], x[color])) in new_column

Re: Grouping/Ranking of elements

sanalytics — Wed, 30 Oct 2024 10:47:28 GMT

Thanks AlienSx MFelix shafiz_p for all your solution.

I have done a small try

bartek_pepper

Create a Custom column by using below code

if Table.Contains([Count],[color ="brown"]) then {"brown"} else List.Distinct([Count][color])

Full code

let Source = Table2, #"Grouped Rows" = Table.Group(Source, {"letter"}, {{"Count", each _, type table}}), Custom2 = #"Grouped Rows", #"Added Custom" = Table.AddColumn(Custom2, "new Column", each if Table.Contains([Count],[color ="brown"]) then {"brown"} else List.Distinct([Count][color]) ), #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"color"}, {"color"}), #"Expanded Custom" = Table.ExpandListColumn(#"Expanded Count", "new Column") in #"Expanded Custom"

output

Regards

sanalytics

Re: Grouping/Ranking of elements

Anonymous — Thu, 31 Oct 2024 07:35:52 GMT

Hi bartek_pepper ,

Thanks for all the replies!
bartek_pepper , Have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

Re: Grouping/Ranking of elements

bartek_pepper — Tue, 05 Nov 2024 11:17:40 GMT

Hi!

Thank you so much for your input. Indeed, it helped but I have not investigated yet why for some of them this is not working but maybe it is just a matter of data. But the idea in general is really good!

Re: Grouping/Ranking of elements

bartek_pepper — Tue, 05 Nov 2024 11:20:09 GMT

Thank you so much everyone!