Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Fill In Blank with Results from Another Column

I have 2 columns: createdby_id, NAME. The goal is to fill the NULL (blank) values in the NAME column with the value from the createdby_id. Please see below sample table for reference. 

 

How would you do this? Can this be done without having to create another calculated column? I tried to do this with the replace value function but I don't know how to reference the createdby_id and replace in the NAME column.

 

createdby_idNAMETARGET_OUTCOME
Guest Guest
Guest1 Guest1
krbtgt krbtgt
t.joneTom JonesTom Jones
an.franAnne FrankAnne Frank
meg.foxMegan FoxMegan Fox
Need Help
Tutorial Requests
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    Based on the video from Miguel Escobar (video link)

     

    If you do a replace step and then change the text for the column name you should get the expected result.

     

    In my example below I first will replace the null for value COLUMNNAME and then replace that by the text you need to place on the formula bar:

     

    = Table.ReplaceValue(Source,"","COLUMN NAME",Replacer.ReplaceValue,{"NAME"})

    Now replace the "COLUMN NAME" by the code highlited below:

    = Table.ReplaceValue(Source,"",each _[createdby_id],Replacer.ReplaceValue,{"NAME"})

     

    Check the full code below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9NLS5R0oHTSrE6UEFDmKghTDi7KKkkHaQIzgALl+hl5eelAnkh+bkKXkBmMQobpCQxTy+tKDEPKO6Yl5eq4AZkZ6NyQKpyU9P10vIrgBK+qemJeQpuaOzYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [createdby_id = _t, TARGET_OUTCOME = _t, NAME = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",each _[createdby_id],Replacer.ReplaceValue,{"NAME"})
in
    #"Replaced Value"

     

    Regards,

    MFelix

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    Based on the video from Miguel Escobar (video link)

     

    If you do a replace step and then change the text for the column name you should get the expected result.

     

    In my example below I first will replace the null for value COLUMNNAME and then replace that by the text you need to place on the formula bar:

     

    = Table.ReplaceValue(Source,"","COLUMN NAME",Replacer.ReplaceValue,{"NAME"})

    Now replace the "COLUMN NAME" by the code highlited below:

    = Table.ReplaceValue(Source,"",each _[createdby_id],Replacer.ReplaceValue,{"NAME"})

     

    Check the full code below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9NLS5R0oHTSrE6UEFDmKghTDi7KKkkHaQIzgALl+hl5eelAnkh+bkKXkBmMQobpCQxTy+tKDEPKO6Yl5eq4AZkZ6NyQKpyU9P10vIrgBK+qemJeQpuaOzYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [createdby_id = _t, TARGET_OUTCOME = _t, NAME = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",each _[createdby_id],Replacer.ReplaceValue,{"NAME"})
in
    #"Replaced Value"

     

    Regards,

    MFelix