recognise format of code in Power Query to extract

[ooptennoort]

>10k of rows of short sentences have within then, usually, a code. I need to extract this code, but how do I get PQ to recognise its format, which is:

230F-115-04

301F-115-05

30A-115-26

30F-1180-02

30F-118-03

30F-1252-04

130H-1252-05

30F-1252-18

30F-126-01

40G-127-01

30F-108-01

31F-299-91

30F-130-01

30F-131-06

30F-132-01

I've tried with text.before and between and after, but have never gotten just -and all of- the code. Very challenging. Help is very much appreciated (if want I want is possible at all ).

 

[GraH]

Care to explain how this works @Bo_Ry ?

 

[Bo_Ry]

@GraH

please check this article

RegEx in Power BI and Power Query in Excel with Java Script –

You can use Java Script within the Web.Page-function to execute RegEx (regular expressions) on your Power BI or Power Query data:

www.thebiccountant.com

 

[GraH]

@GraH

please check this article

RegEx in Power BI and Power Query in Excel with Java Script –

You can use Java Script within the Web.Page-function to execute RegEx (regular expressions) on your Power BI or Power Query data:

www.thebiccountant.com

Thx, Imke rules again...

 

[ooptennoort]

Hi @ooptennoort ,
Would this do the trick for you?

Book1
	A	B
1	Comment	GetCodes
2	bla blabla bla 230F-115-04 blablablabla blablabla blablablabla blablabla blabla	230F-115-04
3	blabla blablablablablabla blablabla bla 301F-115-05 blabla bla	301F-115-05
4	bla bla bla bla bla bla bla blablablablablablablablablabla 30A-115-26 blablablablablablablabla blablablablablabla blabla	30A-115-26
5	blablablablablabla 30F-1180-02 blablablablablablablablablablablablabla	30F-1180-02
6	blablablablablablablablablabla 30F-118-03 blablablabla 130H-1252-05	30F-118-03, 130H-1252-05
7	blablablablablablablablablablablabla 30F-1252-04 blablablablablablablablablabla	30F-1252-04
8	blablablabla400 blablablablabla 130H-1252-05 blablabla	130H-1252-05
9	5000 blablablablablablablablablablablablablablablablablablabla 30F-1252-18 blablablablablablablablablablablablablablablablabla	30F-1252-18
10	blabla 30F-126-01 blablablablablablablablabla	30F-126-01
11	blablablablablablablablablablablablabla 40G-127-01 blabla	40G-127-01
12	blablablablablablablablablablablablabla 30F-108-01 blablablabla	30F-108-01
13	blablabla 31F-299-91 blablablablablablablablablablablablablablablablabla	31F-299-91
14	blablablablablablablablablablablablablablablablablablabla 30F-130-01 blablablablablablablablablablablablablablabla	30F-130-01
15	blablablablablablablablablablablablablablablabla 30F-131-06 blablablablablablablablabla	30F-131-06
16	301F-115-05 blabla 30F-132-01 blablablablablablablablablablablabla 105	301F-115-05, 30F-132-01
17	30F-130-01 blablabla	30F-130-01
18	blablabla 30F-130-01	30F-130-01
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comment", type text}}),
    GetCodes = Table.AddColumn(#"Changed Type", "GetCodes", each let Words = Text.Split([Comment]," "),
Codes = List.Select(Words, each List.AllTrue({ Text.Contains(_, "-"),
Text.PositionOfAny(_, {"0".."9"} ) <> -1,
Text.PositionOfAny(_, {"A".."Z"} ) <> -1})),
Combine = Text.Combine(Codes, ", ")
in
Combine)
in
    GetCodes

Wow!! Certainly! With the one exception that more often than not a code is followed by a comma (no space). But this I can easily correct of course (with interface). I'll try Bo_Ry's suggestion too, now (before I mark best solution). Thank you!!

 

[ooptennoort]

Use data from GraH

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Regex = Table.AddColumn(Source, "Regex", each  Text.Combine(Json.Document(Web.Page(
    "<meta http-equiv='X-UA-Compatible' content='IE=edge'> <script>
    document.write(JSON.stringify('"& [Comment] &"'.match(/\d+[A-Z]-\d+-\d+/g)));
    </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}),", "))
in
    Regex

Power Query Regex.xlsx
	A	B
1	Comment	Regex
2	bla blabla bla 230F-115-04 blablablabla blablabla blablablabla blablabla blabla	230F-115-04
3	blabla blablablablablabla blablabla bla 301F-115-05 blabla bla	301F-115-05
4	bla bla bla bla bla bla bla blablablablablablablablablabla 30A-115-26 blablablablablablablabla blablablablablabla blabla	30A-115-26
5	blablablablablabla 30F-1180-02 blablablablablablablablablablablablabla	30F-1180-02
6	blablablablablablablablablabla 30F-118-03 blablablabla 130H-1252-05	30F-118-03, 130H-1252-05
7	blablablablablablablablablablablabla 30F-1252-04 blablablablablablablablablabla	30F-1252-04
8	blablablabla400 blablablablabla 130H-1252-05 blablabla	130H-1252-05
9	5000 blablablablablablablablablablablablablablablablablablabla 30F-1252-18 blablablablablablablablablablablablablablablablabla	30F-1252-18
10	blabla 30F-126-01 blablablablablablablablabla	30F-126-01
11	blablablablablablablablablablablablabla 40G-127-01 blabla	40G-127-01
12	blablablablablablablablablablablablabla 30F-108-01 blablablabla	30F-108-01
13	blablabla 31F-299-91 blablablablablablablablablablablablablablablablabla	31F-299-91
14	blablablablablablablablablablablablablablablablablablabla 30F-130-01 blablablablablablablablablablablablablablabla	30F-130-01
15	blablablablablablablablablablablablablablablabla 30F-131-06 blablablablablablablablabla	30F-131-06
16	301F-115-05 blabla 30F-132-01 blablablablablablablablablablablabla 105	301F-115-05, 30F-132-01
17	30F-130-01 blablabla	30F-130-01
18	blablabla 30F-130-01	30F-130-01
Sheet1

Wow!! This method is even better in that it allows (omits) for comma's that follow the code (no space)!! Thank you. I'll try to find time to read Imke's article next week... Though this is the best solution, it would be really best if I can understand the code ;p Thank you (both!)!! Amazing!

 

[Bo_Ry]

The most important is this line

document.write(JSON.stringify('"& [Comment] &"'.match(/\d+[A-Z]-\d+-\d+/g)))

where \d+[A-Z]-\d+-\d+ is regex Pattern to capture string

\d+ Any number more than 1 character
[A-Z] Any from A-Z
- dash

RegExr: Learn, Build, & Test RegEx

RegExr is an online tool to learn, build, & test Regular Expressions (RegEx / RegExp).

regexr.com

 

[GraH]

Wow!! Certainly! With the one exception that more often than not a code is followed by a comma (no space). But this I can easily correct of course (with interface). I'll try Bo_Ry's suggestion too, now (before I mark best solution). Thank you!!

One can add a step to replace "," by " ". That's why I asked for a sample.
But indeed the regex trick is awesome.

 

[ooptennoort]

Trying to use run the Regex trick, I run into a problem right away. Am I right that it is to with my worksheet not being a table. The error text is:

Expression.Error: The field 'Comment' of the record wasn't found.
Details:
Name=Sheet1
Data=

Item=Sheet1
Kind=Sheet
Hidden=FALSE

I've tried replacing bits of the Advanced Editor with the trick code but M isn't straightforward enough for a novice such as myself.
Simplifying my workbook, convert to range etc. might do the trick but... can the Regex trick be altered to be inserted mid Adv.Ed. so to speak?

 

[ooptennoort]

...somehow not all of the error text seems to have uploaded:

Data= should have been followed by [ Table ]