Forum Discussion
Power Query Forumla
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
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.
3 Replies
- HotChilli
Community Champion
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.
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!
