Assign attributes randomly

[ValeriaBreve]

Hello,

I have a product table in PowerQuery with Product Names (~1500 unique), and another table that contains a color scheme that I would like to implement for the products (tableau20, so 20 colors).

How can I attribute randomly one of the 20 values of the "color" table to each one of my products in the "product" table?

Thanks a lot!

Kind regards

Valeria

[Sairam]

Dear AntrikshSharma,

Is it possible to upload sample data to understand the logic of the code

Thanks in advance

Sairam

[AntrikshSharma]

@Sairam Create 2 new queries and paste the below codes in the advanced editors

Products:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdS9CsIwFIbhWymZOzQ5qU1mdXB16VA6iA0itI30B/TuVbpY4YVugfcbDs+QqlJapWof+ymO8fM6XmMfu5eq00oZLILFYsmx7LAUWBwWj0VnnFhBM4NmB80QmiX0muIcbnN7GZZUcHKcPCaTcdKcDCfhZDnlnFjDsIZhDcMawhrCGsIawhrCGsIawhqy1jiEdn6GpTgsnorNsLCEZQnLEpYlLEvYr0R5b0JSxqFtklP3iMMUhvH/Lyo27tzGnefd7315tmFXvwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductKey = _t, Brand = _t, Class = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ProductKey", Int64.Type}, {"Brand", type text}, {"Class", type text}})
in
    ChangedType

Colors:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCigtKshJVYrViVZyrCotgrBCihLzigsSi1LzSsD84MycstQiMNO9KDU1D8wKyMzLhglVghlBqSlg2qkovxyixj0/ByqUk5icDWWVQiyJTM3JyS8HM8MzMktSkWxSgBvpD3RJOlAqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Color = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Color", type text}})
in
    ChangedType

[Sairam]

Thanks  a ton. The turnaround was so fast.

[ValeriaBreve]

Hello @AntrikshSharma , I am officially baffled 🙂 and sorry for the late reply. It works very well. Would you be so kind to explain to me how it works step by step? I don't have enough knowledge at this point to understand it completely but I would very much like to gain it :-). Thanks!

[AntrikshSharma · Accepted solution]

@ValeriaBreve I have split the code intor variables/step and commented the code so that it is easy to understand:

Table.AddColumn (
    Source,
    "Color",
    each
        let
            /*
                Get the Color Column as a list
                You can also use List.Buffer in case the code is slow
                ColorList = List.Buffer ( Colors[Color] )
            */
            ColorList = Colors[Color], 

            // Count the colors, returns 16 in my data
            ColorCount = List.Count ( ColorList ),

            // Get a random number
            RandomNumber = Number.Random (),

            // Need an Integer column
            IntegerColumn = [ProductKey],

            // Again some random number
            Index = RandomNumber * IntegerColumn,

            /* 
                If you divide a random number and get the remainder, 
                the remainder will be between 0 and that number
                Example = 23 / 16 (Color Count) will return 7, 
                remainder of 215356565423 / 16 will be 15
                remainder of 215356565424 / 16 will be 0
            */
            Mod = Number.Mod ( Index, ColorCount ),

            // Rounded the number to get the integer part
            Round = Number.RoundDown ( Mod ),

            /* 
                using Index Lookup we can get the random color from ColorList
                ? is just to ensure if there is an error then return null instead
                You can try yourself -> ColorList{Round + 1} 
            */
            Result = ColorList{Round}?
        in
            Result,
    type text
)

[ValeriaBreve]

Beautiful, thank you so much for taking the time to explain! 🙂 It is clear now!

[AntrikshSharma]

@ValeriaBreve Assuming you have a ProductKey or any integer column in the Products table and the other Query is named Colors with Color column, you can try this:

Table.AddColumn (
    Source,
    "Color",
    each Colors[Color]{
            Number.RoundDown (
                Number.Mod ( 
                    Number.Random () * [ProductKey], 
                    List.Count ( Colors[Color] ) 
                )
            )
        }?,
    type text
)