Forum Discussion

Centaur's avatar
Centaur
Icon for Helper V rankHelper V
4 years ago
Solved

Conditonal Statement

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...
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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]

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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's avatar
Centaur
Icon for Helper V rankHelper V
4 years ago

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: