Lookupvalue

[Passion_fruit_4]

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.

[tackytechtom]

Hi @Passion_fruit_4 ,

Is it this you are looking for? 

Before:

After:

Here the code in M that you can paste into the advanced editor to figure out the steps:

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/

[edhans]

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.