Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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



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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.