Need Help on Excel power query editor by using M code

 

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