Inserting a formula into Powery Query

New Contributor

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 most of the data in the text is connected with other non-required info.

 

 

Thanks

 

3 Replies

@Shane1408 The PQ equivalent would be Text.Middle

See attached link:

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

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

@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).