Forum Discussion
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,
8 Replies
"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.
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.
A slightly unusual question. You have tried Power Query split by delimiter?
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
