Custom Column: Automatic Assign Random Color to Value

[Turtle1]

Hi,

I was wondering if there is a Way for getting a custum column in which I could assign a color name/code to each unique value in other column. One column contains company names and for each unique company name I want to randomly assign color which then i could use on map legend. I do not want to do that with switch function as list is too long. So need some smarter way to check for unique value and automatically generate random color code.

Any ideas how that could be done?

Thank you and have a nice day!

[Turtle1]

Ha! I overlooked the group by 🙂 many thanks for the idea and support! Much appriciated!

[Turtle1]

Hi @Winniz

That is clever! Thnks for the hint. This already takes me half way there. One unsolved issue is that my list of companies (in your example countries ) is not unique. Company may be listed more then once in the column and if I index it will give new index number to same company/country while I need it to be same to assign same color. Any idea here? how I create unique index value for unique company/country name?

Regards

Dorota

[v-kkf-msft]

Hi @Turtle1 ,

The above way works for non-unique company list. To make the same countries have the same color, I add index column after grouping by Country, and then expand other columns so that the same countries have the same index number.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft · Accepted solution]

Hi @Turtle1 ,

1. Please use the API "https://api.color.pizza/v1/" to import the color code, and then add the index column.

The complete code is as follows.

let
    Source = Json.Document(Web.Contents("https://api.color.pizza/v1/")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded colors" = Table.ExpandListColumn(#"Converted to Table", "colors"),
    #"Expanded colors1" = Table.ExpandRecordColumn(#"Expanded colors", "colors", {"name", "hex", "rgb", "hsl", "lab", "luminance", "luminanceWCAG"}, {"colors.name", "colors.hex", "colors.rgb", "colors.hsl", "colors.lab", "colors.luminance", "colors.luminanceWCAG"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded colors1",{"colors.name", "colors.hex"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

2. Group your data by [Country], then add index column. Then merge the color table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vzSspqlRwVNJRSlSK1UGIOAFFklBEnIEiySgiLkCRFBQRV6BIKoqIG1AkDUXEHSiSjiLiARTJUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, city = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"city", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"AllRows", each _, type table [Country=nullable text, city=nullable text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Added Index", "AllRows", {"city"}, {"city"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded AllRows", {"Index"}, Query1, {"Index"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"colors.hex"}, {"colors.hex"})
in
    #"Expanded Query1"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.