SOLVED

Power Query If Statement

Steel Contributor

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

@Tony2021 

 

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 (Steel Contributor)
Solution

@Tony2021 

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

1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 

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"

View solution in original post