Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-yetao1-msft
Community Support
Community Support

Hi @Anonymous 

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
v-yetao1-msft
Community Support
Community Support

Hi @Anonymous 

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.

HotChilli
Super User
Super User

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

"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
Not applicable

 

 

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

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
Not applicable

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

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.