Apr 19 2021 03:05 AM
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
Apr 19 2021 03:17 AM
@Shane1408 The PQ equivalent would be Text.Middle
See attached link:
Apr 19 2021 03:30 AM
Apr 19 2021 04:31 AM
@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).