Grouping/Ranking of elements

[bartek_pepper]

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

[shafiz_p · Accepted solution]

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

[MFelix · Accepted solution]

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"

[AlienSx · Accepted solution]

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

[bartek_pepper]

Thank you so much everyone!

[v-junyant-msft]

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

[sanalytics · Accepted solution]

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

[AlienSx · Accepted solution]

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

[MFelix · Accepted solution]

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"

[bartek_pepper]

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!

[shafiz_p · Accepted solution]

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