3

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

DamianD
  • 43
  • 5

1 Answers1

3

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}
Davide Bacci
  • 16,647
  • 3
  • 10
  • 36
  • Hello Davide, this solution worked perfectly for almost all lines, unfortunately we have some exemptions. I added screenshot into my original comment. 1) One 23PL number has a unwanted space inside, so the Text.Split split it unfortunately. 2) One 23PL number was returned with phrase "decision", dont know why as there is actually space between the number and phrase "decision". Do you have any advice? If not - I will work with the code you suggested, it's big improvement either way – DamianD Jul 04 '23 at 09:57
  • Basically I just removed the value "Decyzja" so it's okay, but I am wondering how to deal with this one case where the number 23PL..... has a unwanted space inside... any suggestion? – DamianD Jul 04 '23 at 10:08
  • This isn't a technical issue but rather a logical one. The problem is where should a break happen - after 14 chars but sometimes 15 chars but there is no rule to say which? It is hard to improve this further without hard rules. – Davide Bacci Jul 04 '23 at 10:13
  • Usually it is 14 chars, but for just one scenario someone made a typo and issued the number with the space inside. Lets leave it as it is, thank you Davide for your support. Have a nice day! – DamianD Jul 04 '23 at 10:53
  • Hello Davide, I added one more question. would you be able to take a look on it? Few rows cannot be filtered, I am wondering what is the reason. – DamianD Jul 04 '23 at 13:11