Forum Discussion

ashraf999's avatar
ashraf999
Regular Visitor
3 years ago
Solved

Problem with Nested Power Query If Statement

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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

ams1's avatar
ams1
Responsive Resident
3 years ago

 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's avatar
ashraf999
Regular Visitor
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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