Replace null values by random value from list using lookup in Power Query

[BBrak]

hi all, Power Query question here: simplified use case as per screenshot below; I would like to replace the missing values in the column Section with randomly picked values from a list.

In Power Query I can achieve this using a hardcoded ReferenceList:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNV0lEComBDQ0Mjc6VYHTQhC4SQowlM0BJDnbEBWCjEyAhJzBCLmBEWMWMkMSOYoIlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Section = _t, Article = _t]),
ReferenceList = {"A1", "A2", "A3", "A4", "A5", "1", "2"},
#"ReplaceNullsWithRandomText" = Table.TransformColumns(
Source,
{
{"Section", each if _ = null or _ = "" then ReferenceList{Number.RoundDown(Number.RandomBetween(0, List.Count(ReferenceList)))} else _}
}
)
in
#"ReplaceNullsWithRandomText"

However, in reality for each row the random value to be picked should be based on the value in the [Location] column.

In Excel I could accomplish it like this:

Any idea how to approach this?

Thanks, Bastiaan
 

[BBrak · Accepted solution]

UPDATE: I have found a working solution, as per below. Only difference is that I have to add as a new column because replacing in existing column using Table.TransformColumns function results in an 'Expression.Error: We cannot apply field access to the type Null.' error.

If anyone knows another (more elegant?) solution, feel free to share.

Bastiaan

let
    MainTable = Table.FromRecords({
        [Location = "N5", Section = null, Article = "S11127"], 
        [Location = "N5", Section = "", Article = "S11128"], 
        [Location = "N5", Section = "A4", Article = "S11129"], 
        [Location = "N5", Section = null, Article = "S11130"],
        [Location = "T22", Section = null, Article = "S11131"],
        [Location = "T22", Section = "", Article = "S11133"],
        [Location = "T22", Section = null, Article = "S11133"],
        [Location = "T22", Section = "2", Article = "S11134"]
        }),
    ReferenceTable = Table.FromRecords({
        [Location = "N5", Section = "A1"], 
        [Location = "N5", Section = "A2"],
        [Location = "N5", Section = "A3"],
        [Location = "N5", Section = "A4"],
        [Location = "N5", Section = "A5"],       
        [Location = "T22", Section = "1"],
        [Location = "T22", Section = "2"]
        }),
    
    RandomValue = (Row) =>
        let
            FilteredReferenceTable = Table.SelectRows(ReferenceTable, each [Location] = Row[Location]),
            RandomIndex = Number.RoundDown(Number.RandomBetween(0, Table.RowCount(FilteredReferenceTable))),
            SelectedValue = FilteredReferenceTable{RandomIndex}[Section]
        in
            SelectedValue,

    #"AddedCustom" = Table.AddColumn(MainTable, "Section NEW", each if [Section] = "" or [Section] = null then RandomValue(_) else [Section])
in
    #"AddedCustom"

[RossEdwards]

To help get you to a solution, lets ignore the null value part of the issue because thats the easy bit.

What is your algorithm for assigning this "random" value to any given record?  The reason I ask is that an easy solution can be to create a new column that assigns this "random" value to every record.  Then you create a final 3rd column that uses the logic of "if [Section] = null then [Random Value] else [Section]"

[BBrak]

hi Ross, thanks for your time, I'm using the Table.TransformColumns function, then within that the code that assigns the random value is this part:

ReferenceList{Number.RoundDown(Number.RandomBetween(0, List.Count(ReferenceList)))}

This would be a solution if the ReferenceList was static, didn't contain too many values and the value to be picked wasn't based on any other value. Unfortunately none of these are true, in particular, the value in the [Location] column should filter the ReferenceList to a subset before the random value is picked. The picking a random value is covered by the code above.

Cheers, Bastiaan