Adding Custom Column - Parsing Existing Column

[mrvamsidhar]

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

COLUMN1	COLUMN2
GZE-ABC-1234(SUBSCRIPTION)	GZE-ABC-1234
\XYZ\GZE-ABC-5678	GZE-ABC-5678
GZE-DEF-1234(SUBSCRIPTION1)	GZE-DEF-1234
\XYZ1\GZE-GHI-5678	GZE-GHI-5678

[dkay84_PowerBI]

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 · Accepted solution]

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