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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrvamsidhar
Helper I
Helper I

Adding Custom Column - Parsing Existing Column

Hello, I am having below Table with Column1. I need to add a Custom Column (Column2) by Parsing Column1. In below table,the cell values are not in consisten format.I need to parse each value. If current cell value contains "(" then I need to pull Left side of cell value. If current cell contains "/" I need to pull right side of cell value. This is just a sample and I am having lot of variances like this. Up until now, I am using macros. But I want to convert this in to Power Query. Is there any way I can Parse this? I know we cannot user VBA in power Query. Any Input?

 

https://1drv.ms/i/s!AuLElRm7ChiimDSlfUc2pxw1sEl

 

COLUMN1COLUMN2
GZE-ABC-1234(SUBSCRIPTION)GZE-ABC-1234
\XYZ\GZE-ABC-5678GZE-ABC-5678
GZE-DEF-1234(SUBSCRIPTION1)GZE-DEF-1234
\XYZ1\GZE-GHI-5678GZE-GHI-5678
1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

Hi @mrvamsidhar

 

Check  out

 

http://stackoverflow.com/questions/39325985/power-bi-query-extract-text-between-delimiters-to-a-new-...

 

and develop on it

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2

You could do all this in the query editor. Some nested logic using custom M could do it, but a lazy way would be to use the split by delimiter function. First, I am assuming that each record only needs to be split once, and the condition you described is mutually exclusive. The process is as follows: split at the left most occurence of "(". Any rows where this condition doesn't exist will return null. Next, split the original column by "\" at the right most occurrence. Finally, if needed, you can add a custom column that uses If then logic to pull all the values into one column.
CheenuSing
Community Champion
Community Champion

Hi @mrvamsidhar

 

Check  out

 

http://stackoverflow.com/questions/39325985/power-bi-query-extract-text-between-delimiters-to-a-new-...

 

and develop on it

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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