Forum Discussion
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.
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"
4 Replies
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"- Very nice. I didnt think of that strategy. Crafty. thanks guys. I really appreciate the insight.
Tony2021 , glad to help
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]
