Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to 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.
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.
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.
"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.
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?
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |