Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.