Challenge with split columns by delimiter

[arb1782]

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

[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 · Accepted solution]

Hi @PhilipTreacy @Greg_Deckler 

I've solved this problem now. Thanks for your input.

arb

[v-kelly-msft]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@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 

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

[arb1782]

Hi @PhilipTreacy 

There are 2 sources but the data is in a single database table with another field that implies the source.