Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Proud to be a Super User!
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!
@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?
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?
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
Proud to be a Super User!
There are 2 sources but the data is in a single database table with another field that implies the source.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |