Forum Discussion
Extracting a string of text from a string
Hi,
I have a column (Column1) in my PowerBI dataset which contains a large string - it is essentially a list of tag/name pairs.
Column1
cost_centre:c1, application:rediscache, environment:production, business:digital
cost_centre:c2, application:SQLDB, environment:qa, business:marketing
cost_centre:c1, application: storage account, environment:dev, business:digital
cost_centre:c3, environment:production, business:manufacturing
I want to extract the value for a particular tag into a custom column (or similar), so I can use elsewhere in the report.
So I'd like to end up with the following, when 'application' is the tag
Column1 | SearchValue |
cost_centre:c1, application:rediscache, environment:production, business:digital | rediscache |
cost_centre:c2, application:SQLDB, environment:qa, business:marketing | SQLDB |
| cost_centre:c1, application:storage account, environment:dev, business:digital | storage account |
| cost_centre:c3, environment:production, business:manufacturing | Unknown |
Any help is greatly appreciated 😀
Hello Anonymous !
On Power Query Editor go to Add Column and choose Extract > Text Between delimiters.
On Start Delimiter put: "application:"
On End Delimiter put: ","
Hello Anonymous ! I'm glad it worked 🙂
Taking into consideration what you just said, the records that don't have application tag must be empty on the output column. To put it as "Unknown" just select the output column go to Transform > Replace Values and do the following:
5 Replies
- JorgePinho
Solution Sage
Hello Anonymous !
On Power Query Editor go to Add Column and choose Extract > Text Between delimiters.
On Start Delimiter put: "application:"
On End Delimiter put: ","
- jdbuchanan71
Super User
I knew there was a way to do it in PowerQuery and that is probably the best place to do it.
There is a DAX solution done as a cacluated column like this that I thought I would share.
SearchValue = VAR _String = "application:" VAR _AppStart = SEARCH ( _String, 'Table'[Column1],, BLANK () ) VAR _ResultStart = _AppStart + LEN ( _String ) VAR _AppEnd = SEARCH ( ",", 'Table'[Column1], _AppStart ) VAR _Characters = _AppEnd - _ResultStart - 1 RETURN IF ( ISBLANK ( _AppStart ), BLANK(), MID ( 'Table'[Column1], _ResultStart, _Characters ) ) JorgePinho - thanks for the advice, that works. 😀
one more question - not all records in my dataset have an 'application:' tag, so I want to set these to have an 'Unknown' value
- JorgePinho
Hello Anonymous ! I'm glad it worked 🙂
Taking into consideration what you just said, the records that don't have application tag must be empty on the output column. To put it as "Unknown" just select the output column go to Transform > Replace Values and do the following:
JorgePinho perfect > great, simple solution, works perfectly 😁
