Extracting partial string from cells in power query

[Nick Saunders]

Hi,

I am trying to extract a partial string of text from a notes column and place that partial string of text into a new column. I am using power query, and I feel like there must be a way to to do this, but I can't quite figure it out. Any ideas?

The attached example spreadsheet has the "original" table on the left, and the "desired outcome" table on the right. (not thru power query, I added that 3rd column manually)

EDIT: As I think about it, being able to filter based on a partial string in power pivot would also work. Whatever is easier.

[TMS]

You could do that with a simple formula, unless you're doing it for the experience/challenge.

Please Login or Register  to view this content.

[AliGW]

What's the logic? I can't help feeling that you have radically over-simplified your sample data, but ...

M Code:

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.

However you can do the latter by using a regular text filter on a regular table directly on the Excel page - no need for any form of pivot!

[AliGW]

By the way, your profile says Excel 2011: as far as I am aware, that's a Mac version that does not support PQ, so what are you using now? Please update your profile with MS365 (which is what I think you have).

[Nick Saunders]

I think you're right, I have excel 365 - not sure why I thought I had excel 2011.

Thanks for the help, I think that does what I need!

I could do the same thing in a regular table, sure, but in my "real" excel spreadsheet, I am querying dozens of spreadsheets that total to several thousand rows of data, and then I am putting it into power pivot to do some other excel wizardry before generating some pretty tailored reports. This works perfectly for my needs. Thanks again!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.