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, I am a novice user.
I have 2 fields:
Invoice Amount
USD Amount
If USD Amount is Null then I want to show Invoice Amount otherwise, show the USD Amount.
I think this is a conditional field but I am not sure how to enter it.
If could kindly assist.
Let me know if you need anything else.
thank you.
Solved! Go to Solution.
Insert a Custom column and paste following (Add column menu - Custom column)
= [USD Amount]??[Invoice Amount]
You can also put following formula as an alternative
= if [USD Amount]=null then [Invoice Amount] else [USD Amount]
As an alternative, you can use coalescing operator. Hence, use following
= [USD Amount]??[Invoice Amount]
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRV0lFSitWJVgLSRgYwFpg2NgCyjI2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"USD Amount" = _t, #"Invoice Amount" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"USD Amount", Int64.Type}, {"Invoice Amount", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each [USD Amount]??[Invoice Amount])
in
#"Added Custom"
Note: I am using Power Query not Power BI.
Insert a Custom column and paste following (Add column menu - Custom column)
= [USD Amount]??[Invoice Amount]
You can also put following formula as an alternative
= if [USD Amount]=null then [Invoice Amount] else [USD Amount]
Adding a Custom Column worked. I note that if I add a Conditional Column the word null must be lower case.
here is what I used:
= if [USD Amount]=null then [Invoice Amount] else [USD Amount]
I discovered its CASE SENSITIVE, which is annoying.
thank you very much for the help.
Hi Vijay, that seemed to work. How could I use that solution though? I was hoping for something simpler (ie adding a conditional column). Sorry but my experience with power query is very basic.
here is the output of the blank query:
equals operator followed by "null" (without quotes) as value should do it.
Otravers,
I had actually tried prior to posting this question but apparently you cant enter a value of NULL. It must be a value. Any other suggestions adding a Conditional Column?
The M language in Power Query is case sensitive, I wrote "null" (not "NULL") for a reason and what I suggested works as demonstrated here:
ahh I see now. Its case sensitive. I confirm I do not get an error but I do if any letter is capitalized. Geez that is annoying.
You can do so from the Power Query UI as explained here:
https://docs.microsoft.com/en-us/power-query/add-conditional-column
Hi, thank you. I am familiar with at least that much of it but my problem is how do I add a condition as NULL? The value in the table is NULL and I don't see NULL as being one of the operators. Thank you
Covering 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.