Collect some variables (mix of number and text) from long text

[Cream_17]

Hello guys, I need your help.

As Quality Engineer, I need to collect "Error Code" informed by our dealer. 

As you can see on below table, I need to collect red text from long text. "Desired output" column is the output I wanted.

If you know the the method I can use to extract the code (red text in "Dealer comment"), please tell me how I can do that.

Basicly, the error code we set (for our dealder) always contained by : Bxxxx (4 digit "x" is always number type)

[olgad · Accepted solution]

In Power Query right click on the comment column, add column from examples and start typing in value B2578 hit enter, probably starting from the 2 nd row it will be wrong extraction, type in in the second row you red text B2666 hit enter. After the 3rd-5th time when you go to row 3 then 4 , Power Bi will comprehend the logic and will extract everything correctly.

[Syndicate_Admin]

Use a text editor or programming language that supports regular expressions, such as Python or JavaScript.
Write a regular expression that matches the pattern of the variables you want to extract. For example, if you want to extract a date in the format "dd/mm/yyyy," you could use the regular expression "\d{2}/\d{2}/\d{4}".
Use the regular expression to search for matches in the long text. Depending on your programming language, you may need to use a specific function or method to do this.
Once you have found matches for your regular expression, extract the relevant information from each match and store it in a variable or data structure. This will depend on the specific format and structure of the data you are working with.

[Jakinta · Accepted solution]

You can try with step below.

Note: Text.Combine is used to handle cases if you have multiple Bxxxx in text field (The result would be: Bxxxx, Bxxxx,...). If there will never be such cases Text.Combine could be omitted.

= Table.AddColumn(Source, "Desired Output", each Text.Combine (List.RemoveNulls (List.Transform (Text.Split ([Dealer Comment], " "), each if Text.Start(_,1)="B" and Text.Length(_) > 4 and Text.Length(Text.Select(Text.Range(_,1,4), {"0".."9"}))=4 then "B" & Text.Range(_,1,4) else null ) ) , ","))

[Cream_17]

Dear Jakinta, thanks for your another help.

[olgad · Accepted solution]

In Power Query right click on the comment column, add column from examples and start typing in value B2578 hit enter, probably starting from the 2 nd row it will be wrong extraction, type in in the second row you red text B2666 hit enter. After the 3rd-5th time when you go to row 3 then 4 , Power Bi will comprehend the logic and will extract everything correctly.

[Cream_17]

Dear Olgad thanks for your comment. I think it works with my data.