Power Query Forumla

[Tom123456789]

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

[v-stephen-msft · Accepted 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.

[Tom123456789]

Thank you very much!

[HotChilli]

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.