cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arb1782
Helper III
Helper III

Challenge 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:

 

CategoryValue
Category 1X, x and x
Category 2Y, y

 

etc

 

What I've got - Format 1

Category 1Category 2Category 3Category 4Category 5Category 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 1Category 2Category 3Category 4Category 5Category 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

1 ACCEPTED SOLUTION

Hi @PhilipTreacy @Greg_Deckler 

 

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

 

arb

View solution in original post

7 REPLIES 7
PhilipTreacy
Super User III
Super User III

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi @PhilipTreacy @Greg_Deckler 

 

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

 

arb

View solution in original post

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

@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?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi @PhilipTreacy 

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.