cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marck
Helper I
Helper I

How to split strings by a delimiter?

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,

1 ACCEPTED SOLUTION
Ailsa-msft
Community Support
Community Support

Hi @Marck 

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

Ailsa-msft_0-1617183701541.png

Ailsa-msft_1-1617183701546.png

 

The effect is as shown:

Ailsa-msft_2-1617183701547.png

Ailsa-msft_3-1617183701548.png

 

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.

View solution in original post

8 REPLIES 8
Ailsa-msft
Community Support
Community Support

Hi @Marck 

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

Ailsa-msft_0-1617183701541.png

Ailsa-msft_1-1617183701546.png

 

The effect is as shown:

Ailsa-msft_2-1617183701547.png

Ailsa-msft_3-1617183701548.png

 

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.

View solution in original post

HotChilli
Super User II
Super User II

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
Super User II
Super User II

"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.

 

 

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
Super User II
Super User II

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 

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
Super User II
Super User II

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

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors