Page 1 of 1

PowerPivot: Help with conditional split column

Posted: Sun Nov 17, 2019 9:10 pm
by BiKri
Hi community,

being almost new to PowerPivot I face now a problem when splitting a column by using a delimiter.
Giving you an example is probably best. I do have a large csv file containing among others a column called ingredients which I want to split in rows. As delimiter I need to use the comma but if the comma is within brackets it shouldn't split.

How can I get the result from having:
pea protein isolate, rice powder (dried rice syrup, rice starch, rice flour), coconut milk powder

Into:
Pea Protein isolate
rice powder (dried rice syrup, rice starch, rice flour)
coconut milk powder

Instead of:
Pea Protein isolate
rice powder (dried rice syrup
rice starch
rice flour)
coconut milk powder

Thanks

Re: PowerPivot: Help with conditional split column

Posted: Sun Nov 17, 2019 10:14 pm
by RamanaV
Hi,

Actually it is difficult to give an answer with just one value.
However assuming that all the values in the column are in the same format, I have provided a solution.

Please note that we cannot have such a conditional split (do not split if , is within parenthesis)

A better solution is extracting text.
Please look at the sample and the Power Query commands I used.
Hope it help

Ramana

Re: Power Query: Help with conditional split column

Posted: Sun Nov 17, 2019 11:30 pm
by BiKri
Hi Ramana,

thanks for your suggestion!
I'll give it a try