Forum Discussion

Copper Contributor

Apr 19, 2021

Inserting a formula into Powery Query

Hi All, Please can i ask for help on how to insert a formula into a Power Query as described below: I need to insert a "MID" formula to extract my Stock No: I can't use a delimiter as mo...

Stock No example.docx64 KB

excel

Riny_van_Eekelen

Platinum Contributor

Apr 19, 2021

Shane1408 The PQ equivalent would be Text.Middle

See attached link:

https://docs.microsoft.com/en-us/powerquery-m/text-middle

Shane1408

Copper Contributor

Apr 19, 2021

Hi Riny,

Thanks for the link.

Relatively new with Power Query. When & where do i insert this. would it be in the Power Query i'm running or do i need to create a new table from "Other Sources"?

A screenshot would be very helpful.

Thanks

Riny_van_Eekelen
Platinum Contributor
Apr 19, 2021
Shane1408 You'd add a step that adds a custom column.

Select the "Add Column" tab, then the "Custom column" icon and write a formula like:
= Text.Middle ( [Links], 3, 6 )
assuming that the column called "Links" and that you want to (as an example) extract 6 characters, starting from the 4th(!). Note that PQ's counting is zero-based. Thus the 4th character has index number 3 as in 0, 1, 2, 3. In stead of writing the column name you can double click on the column name in the formula editor. It's quite intuitive.
If you run into problems, come back here with an example of your data file (remove any confidential information though). So much easier to work with real data than with a picture. And indicate where the Stock Numbers are actually "hidden" in the data (which column, which characters).