Forum Discussion
Helper IIIChallenge with split columns by delimiter
Hi
I have data across 6 columns and the data is in two formats depending on the data source. I want to achieve the following:
| Category | Value |
| Category 1 | X, x and x |
| Category 2 | Y, y |
etc
What I've got - Format 1
| Category 1 | Category 2 | Category 3 | Category 4 | Category 5 | Category 6 |
| ["X, x and x"],["Y, y], ["z - z, z"] | ["X, x and x"] | ["X, x and x"],["Y, y], ["z - z, z"] | ["X, x and x"] | ["X, x and x"],["Y, y] | ["X, x and x"] |
What I've got - Format 2
| Category 1 | Category 2 | Category 3 | Category 4 | Category 5 | Category 6 |
X, x and x, Y, y, z - z, z | X, x and x, Y, y | X, x and x | X, x and x, Y, y, z - z, z | X, x and x, Y, y | X, x and x, |
My problem - please help
Splitting by comma for a column delimiter works for format 1, but not for format 2 because the values themselves contain commas. How can I split out the second format in a way that works?
Thans
arb
7 Replies
- PhilipTreacy
Super User
Hi arb1782
If you've got data from 2 sources in different formats, why not take the data from each source into its own query and avoid this problem?
Phil
arb1782Hi PhilipTreacy
There are 2 sources but the data is in a single database table with another field that implies the source.
- Greg_Deckler
Community Champion
arb1782 Is the data in format 2 all in a single row? If so, could you split by line break? If they are in multiple rows, seems like you wouldn't have to split anything, just unpivot the columns?
- arb1782
Hi Greg
They're in a single row yes. There doesn't appear to be an option to split by line break in the Split columns window. Any ideas?
- PhilipTreacy
Hi arb1782
Can you please supply examples of the data as it actually appears once brought into PBI with data in Format1 and Format2 in the same columns. Showing it as 2 separate formats has confused me, I don't understand how the data is really structured.
Can you expand on how you have 1 table but 2 sources? How are you actually querying this table?
Phil
- arb1782
v-kelly-msftCommunity Support
Hi arb1782 ,
Glad to hear that your issue is solved now,could you pls mark the reply as answered to close it?
Thank you.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
