PowerPivot: Help with conditional split column

Anything related to PowerPivot and DAX Formuale
BiKri
Posts: 2
Joined: Sun Nov 17, 2019 8:58 pm

PowerPivot: Help with conditional split column

Postby BiKri » Sun Nov 17, 2019 9:10 pm

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

RamanaV
Posts: 39
Joined: Thu Oct 19, 2017 12:57 pm

Re: PowerPivot: Help with conditional split column

Postby RamanaV » Sun Nov 17, 2019 10:14 pm

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
Attachments
Extracting Text.xlsx
(19.05 KiB) Downloaded 6 times
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

BiKri
Posts: 2
Joined: Sun Nov 17, 2019 8:58 pm

Re: Power Query: Help with conditional split column

Postby BiKri » Sun Nov 17, 2019 11:30 pm

Hi Ramana,

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


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest