Conditonal Statement

[Centaur]

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. 

[Vijay_A_Verma]

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"

[Centaur]

Note:  I am using Power Query not Power BI.   

[Vijay_A_Verma · Accepted 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]

[Centaur]

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.  

[Centaur]

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: 

[otravers]

equals operator followed by "null" (without quotes) as value should do it.

[Centaur]

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? 

[otravers]

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:

[Centaur]

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.  

[otravers]

You can do so from the Power Query UI as explained here:

https://docs.microsoft.com/en-us/power-query/add-conditional-column

[Centaur]

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