How to split strings by a delimiter?

[Anonymous]

Hello,

I have a filed called campaign name and the most recent campaign names have their strings  delimited by "-" 

For instance I have a campaign called:  London_Car-Blue-March_2021 and   I would like to split the campaign  by"-" 

Hence, having a split for:

London_Car

Blue

March_2021

Somone can help me with this? I have tried to identfy similar topics in the community but at the moment I did not find any suggestions useful ro my scenario.

Thanks,

[Anonymous · Accepted solution]

Hi @Anonymous 

You can use Split Column in Query Editor .Enter “-” to split value .

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

The code will be different for each column.  

The 2nd one you'll probably use MID, the 3rd one RIGHT.

I advise the use of variables to help write each formula and to help test.

[HotChilli]

"Hence, power Query is not available for this table created" - Do the union in Power Query.  Do the split in Power Query.

Is it possible in DAX? yes, you would create a new column using LEFT, FIND etc then do it again to create another column but it's a lot simpler in Power Query.

[Anonymous]

I have used the following DAX:

LEFT( 'All Data'[Campaign name],
FIND("-", 'All Data'[Campaign name],1,
LEN('All Data'[Campaign name])))

With regards, London_Car-Blue-March_2021 as  campaign name example: 

For the first split column I am getting London_Car

However, when try to split the value for the second position (2), I am still getting the same string value shown in the first column: London_Car and not Blue. And same issue for the third split column.

Is there something I am doin wrong?

I just want to split the string each time there is the character "-" based on its position.

[HotChilli]

Not really.  In the intermediate step between Get Data and powerbi front end where you can shape and 'Transform Data'

It's all here:

https://docs.microsoft.com/en-us/power-query/split-columns-delimiter 

[Anonymous]

Hi,

Just for clarification, I am using a table created from a union of different data sources. Hence, power Query is not available for this table created. Is there any way I can use split the desired column by a delimiter in this case"-" without using Power Query?

[HotChilli]

A slightly unusual question.  You have tried Power Query split by delimiter? 

[Anonymous]

Sorry I am not  sure what is it i am new into Powerbi. However,  I have tried your suggestion but I am not able to use the split function from the "Transform data" tab as I am using a direct query data source