Forum Discussion

Passion_fruit_4's avatar
Passion_fruit_4
New Member
3 years ago

Lookupvalue

Hello Community,   My end goal is that for any null value to retrieve the value from the previous column. For example, null from Stars.2 row 2 = Stars:Chris Wood, Sarah Michelle Gellar, Lena Headey...
Need Help
tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost Valuable Professional
3 years ago

Hi Passion_fruit_4 ,

 

Is it this you are looking for? 

 

Before:

 

 

After:

 

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjICsvJKc3LAHGNkjgmQ4wRmmcJZZsgKzIEcZzDLAlnYEpljaIDCA9nrAmEaIZgIa2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Column2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", Int64.Type}, {"Column2", type text}}),    
    #"Replace Values" = Table.ReplaceValue(#"Changed Type",each [Column2],each if [Column2] is null then [Column1] else [Column2],Replacer.ReplaceValue,{"Column2"})

in
    #"Replace Values"

 

Hope this helps!

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/