Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Apologies if this has been asked before, but as a newbie to all things Power BI this is proving a bit of a pain.
The data I'm working with needs a certain bit of tidying before it's used. One of the the things that is proving difficult is extracting a certain text string from another - it's easy enough to do in Excel/Access but I'm strugging with it here.
What I have is this....
Activity | CR |
BAU SBCR811c-i Test | SBCR811c-i |
R3 SBCR1022b Design | SBCR1022b |
R3 SBCR1227 Requirements Gathering | SBCR1227 |
BAU SBCR811c-i | SBCR811c-i |
and in Excel =IFERROR(LEFT(RIGHT(A2,LEN(A2)- FIND("SBCR",A2,1)+1), FIND(" ",RIGHT(A2,LEN(A2)- FIND("SBCR",A2,1)+1))),RIGHT(A2,LEN(A2)- FIND("SBCR",A2,1)+1)) does the trick
I've tried doing it as the data is imported but it gets messey pretty quickly.
I've also tried adding a measure using ....
IsCR = if(FIND("SBCR",[Activity],1,-1)>0, RIGHT([Activity],LEN([Activity])-FIND("SBCR",[Activity],1,-1)), "")
This gets me the first part but I'm struggling to knock off the text string at the end.
Unfortunately I can't ask that the data is aperated before I get it..
Regards,
Tony
Solved! Go to Solution.
Hi tonyenglish,
You want to extract substring with the beginning of “SBCR” from [Activity], right?
Click Query Editor->Transform->Split Column like belew.
Column [Activity.2] is what you want.
Regards,
Jimmy Tao
Hi tonyenglish,
You want to extract substring with the beginning of “SBCR” from [Activity], right?
Click Query Editor->Transform->Split Column like belew.
Column [Activity.2] is what you want.
Regards,
Jimmy Tao
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |