Search code examples
excelpowerbipowerquerymphrase

PowerQuery - Find a phrase and return a word containing that phrase

I have a column in which the phrase "23PL..........") occurs in various random places (sometimes at the beginning, sometimes at the end, sometimes in the middle.) The phrase 23PL is followed by a random string of 14 characters.

I would like to create a column or adjust existing column to show only just whole phrases containing 23PL+14 characters.

I.e. from the column with the field "blah blah blah 23PL330000A7657895 blah blah". - I would like to get to "23PL330000A7657895".

Something along the lines of "find all words that contain a total of 23PL and return those words)

I've been messing around with the "extract" function, "text.startwiths", "text.contains" - I've tried to weave an "if" into it, but nothing works for me.

Do you have any ideas?

Thank you in advance.

Answering to Davide, we faced two more problems, maybe someone would be able to advice how to deal with it?

Two issues

Another question, sorry. For some lines Power Query cannot find the number 23PL. Its on below image. When I filter the column, I got warning "list may not be complete". Do you know why PowerQuery cannot filter this line?

picture issue

Solution

  • Here you go.

    enter image description here

    Add a custom column and type the following:

    enter image description here

    List.Select (Text.Split([Column1]," "), each Text.StartsWith(_, "23PL")){0}