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, Mark Hamill from Stars.1 row 2

 

What needs to change in the following formula to execute the code below successfully.

IF(ISBLANK([STARS.2]]),LOOKUPVALUE('Table'[[STARS.1]],'Table'[Row ID],EARLIER('Table'[Row ID])),[[STARS.2]])

 

 

With appreciation.

 

 

Need Help

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    3 years ago

    Create a new column in Power Query

     

    if [Stars.2] = null then [Stars.1] else [Stars.2]

     

    Then delete Stars.2 and only load Stars.1 and the new column. Or, delete the Stars.1 column if you don't need that in Power BI.

     

    The "null" above is a keyword and must be all lower case. It will turn green in the editor. Cannot be Null or NULL. 

  • tackytechtom's avatar
    tackytechtom
    Most 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/