Problem with Nested Power Query If Statement

[ashraf999]

Hello,

I am not sure where the mistake is in the following nested if statement. The query searches for a string in column3. Then it validates column5 and column6 to decide if the value of column4 is to be replaced.

[ams1]

 I don't see the Else for the IF highlighted in the pic above.

Rule of thumb: nr of IFs should equal nr of ELSEs 😁

[ashraf999]

I tried your suggestion by using this code:

= Table.ReplaceValue(#"Changed Type",each [Column4],
	  each if Text.Contains([Column3],"Instant Payment BSF/MBK") then 
	  	      if (Value.Is(Number.FromText([Column5]), type number)) then [Column5] 
	  	      else if (Value.Is(Number.FromText([Column6]), type number)) then [Column6]
              else [Column4]			  
      else [Column4],Replacer.ReplaceValue,{"Column4"})

However, it is not giving the results I want. Here is a sample of the data. As you can see records 21 & 22 should be replaced by the values in Column6:

[Anonymous · Accepted solution]

Hi @ashraf999 ,

Please try:

= Table.ReplaceValue(#"Changed Type",each [Column4],
	  each if Text.Contains([Column3],"Instant Payment BSF/MBK") then 
	  	      if (Value.Is(try Number.FromText([Column5]) otherwise null, type number)) then [Column5]
	  	      else if (Value.Is(Number.FromText([Column6]), type number)) then [Column6]
              else [Column4]			  
      else [Column4],Replacer.ReplaceValue,{"Column4"})

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum