SOLVED

# Power Query If Statement

Frequent Contributor

# Power Query If Statement

Hello,

I am having difficulty updating the field [Amt_New] in the Append Query based on some conditions.

If [Amt1] is 0 and [Amt2] is 0 then leave [Amt_New] as is (no change). basically leave the value in Amt_New if there is already a value there.

If [Amt1] is not 0 and [Amt2] is 0 then update [Amt_New] to [Amt1]

If [Amt1] is 0 and [Amt2] is not 0 then update [Amt_New] to [Amt2]

Basically in english I am updating [Amt_New] to the value of  [Amt1] or [Amt2] when either are not 0 but if there is already a value in Amt_New then keep it.  I hope that makes sense.  Not sure if adding a column would be more simple than trying to update [Amt_New] but I am certainly open to that.

I have included the file.  Please open to the Append Query and you will see [Amt_new].

I tried crafting an If statement but I am not that technical with PQ.

grateful for the assistance.

4 Replies

# Re: Power Query If Statement

Just to show you, you may add a custom column with the following formula as a last step in the existing query...

``````if [Amt_NEW] <> 0
then [Amt_NEW]
else if [Amt1] = 0 and [Amt2] = 0
then [Amt_NEW]
else if [Amt1] <> 0
then [Amt1]
else if [Amt2] <> 0
then [Amt2]
else [Amt_NEW]``````

best response confirmed by Tony2021 (Frequent Contributor)
Solution

# Re: Power Query If Statement

If I understood the logic of source correctly only one of AMT1, AMT2, AMT_New is not zero, thus you may simply sum them and replace with result AMT_New. Plus I'd clean the query, too many unnecessary steps

``````let
Source = Table.Combine({Stampli_PQ, SAP_PQ_Drill_mod, SAP_PQ_Not_DD, BOA_PQ_mod}),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Vendor", "Amount Currency", "Payment Date", "Co Name", "Amount", "Amt", "Amt_NEW"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,0,Replacer.ReplaceValue,{"Amt", "Amt_NEW", "Amount"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Amount", Currency.Type},{"Amt_NEW", Currency.Type}, {"Amt", Currency.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Amount", "Amt1"}, {"Amt", "Amt2"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns",each [Amt_NEW],each List.Sum({ [Amt1], [Amt2], [Amt_NEW] }) ,Replacer.ReplaceValue,{"Amt_NEW"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Amt_NEW", Currency.Type}})
in
#"Changed Type1"``````

# Re: Power Query If Statement

Very nice. I didnt think of that strategy. Crafty. thanks guys. I really appreciate the insight.