Grouping/Ranking of elements

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

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"

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

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