Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
Would anyone be able to turn the following two excel formulas into power query formulas?
=IF(AND(ISNUMBER(VALUE(LEFT($K148,2))),MID($K148,3,1)="C"),"CHANGE",IF(AND(ISNUMBER(VALUE(LEFT($K148,3))),MID($K148,4,1)="C"),"CHANGE",""))
=IF(MID($K148,3,1)="C","0"&LEFT($K148,2),IF(MID($K148,4,1)="C",LEFT($K148,3),""))
Thanks in advance
Solved! Go to Solution.
Hi @Tom123456789 ,
The first formula in Power Query looks like this:
= if Value.Is(Value.FromText(Text.Start([Column1],2)),type number) and Text.Middle([Column1],3,1)="C" then "CHANGE" else if Value.Is(Value.FromText(Text.Start([Column1],3)),type number) and Text.Middle([Column1],4,1)="C" then "CHANGE" else ""
The second formula looks like:
= if Text.Middle([Column1],3,1)="C" then "0"&Text.Start([Column1],2) else if Text.Middle([Column1],4,1)="C" then Text.Start([Column1],3) else ""
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Tom123456789 ,
The first formula in Power Query looks like this:
= if Value.Is(Value.FromText(Text.Start([Column1],2)),type number) and Text.Middle([Column1],3,1)="C" then "CHANGE" else if Value.Is(Value.FromText(Text.Start([Column1],3)),type number) and Text.Middle([Column1],4,1)="C" then "CHANGE" else ""
The second formula looks like:
= if Text.Middle([Column1],3,1)="C" then "0"&Text.Start([Column1],2) else if Text.Middle([Column1],4,1)="C" then Text.Start([Column1],3) else ""
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much!
Hi,
You're probably going to get about 5 minutes of someone's time so it might be better to provide some sample data , showing the desired result with an explanation of how to get there.
That will improve the chances of getting a good response.