Forum Discussion

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago
Solved

Need Help on Excel power query editor by using M code

I have two column Unique key and Reference Key 3 Reference key 3 colunm have some blank rows and need unique key colunm value where in reference key is blank by using M code.   Unique key         ...
  • BA_Pete's avatar
    BA_Pete
    2 years ago

     

    Ok. This code takes the highest [Reference ky 3] value to fill in the blanks:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDLCsIwFER/Rbou9Wbueym4duG29P9/w9S0IJhaV4GcORMm8zzgcb88b1dEgjPKfg7jIGAJLjQs46/YCd5a9B0rmoCbUQo2lsnRY62WJioTCIzQUIRVoikm7CJ2nGmNlaBrC6vrcebUdmo2xcfbbjW3212y2qHsaIudrE4OQQSjYS9af4u7+P/LTpH0HPlytKzLlhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Key" = _t, #"Reference key 3" = _t]),
        repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Reference key 3"}),
    
    //Relevant steps from here ------>
        groupRows = Table.Group(repBlankNull, {"Unique Key"}, {{"data", each _, type table [Unique Key=nullable text, Reference key 3=nullable text]}}),
        addNestedRef3 =
        Table.TransformColumns(
            groupRows,
            {
                "data",
                (x)=> Table.AddColumn(
                    x,
                    "RefKey3",
                    each if [Reference key 3] = null then List.Max(x[Reference key 3])
                    else [Reference key 3]
                )
            }
        ),
        expandData = Table.ExpandTableColumn(addNestedRef3, "data", {"Reference key 3", "RefKey3"}, {"Reference key 3", "RefKey3"})
    in
        expandData

     

    Summary:

    -1- groupRows = Group By [Unique Ref] and add an All Rows aggregated column called "data"

    -2- addNestedRef3 = Add a column to the nested table that gets the highest [Referece key 3] value if the cell is null

    -3- expandData = Expand the nested table columns back out again

     

    Example query output:

     

    Pete