Oct 24 2021 05:06 PM
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.
Oct 24 2021 09:47 PM
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]
Oct 25 2021 02:36 AM
SolutionIf 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"
Oct 25 2021 03:56 AM
Oct 25 2021 02:36 AM
SolutionIf 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"