Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Cream_17
Frequent Visitor

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

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.

 

Cream_17_0-1680640106272.png

 

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)

 

2 ACCEPTED SOLUTIONS
olgad
Super User
Super User

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.


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

Jakinta
Solution Sage
Solution Sage

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 ) ) , ","))

 

 

 

 

 

View solution in original post

5 REPLIES 5
Syndicate_Admin
Administrator
Administrator

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
Solution Sage
Solution Sage

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 ) ) , ","))

 

 

 

 

 

Dear Jakinta, thanks for your another help.

olgad
Super User
Super User

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.


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors