Forum Discussion

BBrak's avatar
BBrak
Icon for Advocate I rankAdvocate I
2 years ago
Solved

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

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.   I...
Need Help
  • BBrak's avatar
    BBrak
    2 years ago

    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"

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    BBrak
    Icon for Advocate I rankAdvocate I
    2 years ago

    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