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         ...
Need Help
  • 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

BA_Pete's avatar
BA_Pete
Super User
2 years ago

 

Ok, let's break this down.

You have [Unique Key] which has a value in every row.

You have [Ref Key 3] which has some missing values.

You want to fill in the blanks in [Ref Key 3] using another column within the same table.

What is the name of the column that you want to get the values from to fill in the blank [Ref Key 3] values?

 

Assume that I have no idea what VLOOKUP does or how it works.

 

Pete

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago

To get the values from Ref key 3, by looking up the unique key in the column. In other words to find the ref key 3 that matches with the unique key of the blank ref key 3

  • BA_Pete's avatar
    BA_Pete
    Super User
    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

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

     

    Ah, ok. I think I understand now.

    How do you want to assign a [Ref 3] value to a blank space when a single [Unique Key] can have multiple [Ref 3] values? Which [Ref 3] value do you want to put in the blank?

     

     

    Pete

  • KuntalSingh's avatar
    KuntalSingh
    Helper V
    2 years ago

    any one reference key can work

  • KuntalSingh's avatar
    KuntalSingh
    Helper V
    2 years ago

    Thanks! Pete