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 all,
Is it possible to transform data from green table (image below) so they will be looking as data in the blue one?
I've tried to unpivot them, but fo course it dosent work. I dont belive in that I will need to split columns by ","... There must be some another way 😉
Maybe you have seen some tutorials regarding it?
Thanks in front for your help!
Solved! Go to Solution.
Yes this dosent work for my dataset ~40k rows, but ..... here is a good solution!
https://www.youtube.com/watch?v=JMOnr3DOqyk
Yes this dosent work for my dataset ~40k rows, but ..... here is a good solution!
https://www.youtube.com/watch?v=JMOnr3DOqyk
Yes, this can be achieved using power query,
Can you copy and paste the green data set in your picture as a reply?
I will share the steps as screenshots.
Thanks and regards.
Hi Shaved,
Can it be a simple excel file or pbix? I will definitely post it 😉
Just copy the cells from A1 to C3 from the excel to the reply box and I will take it from there 😄
So, here it is:
ID | OrderdItem | OrderDates |
1 | 1,2,2 | 2020-01-01, 2020-02-01, 2020-03-01 |
2 | 1,1,3,3 | 2020-01-01, 2020-02-01, 2020-03-01, 2020-04-01 |
First, select the column and click on the split column button in the Home ribbon, Then select by the delimiter
In the popping, window Select the delimiter as a comma and click on advance and under split into choose row radio button. Then click on Okay.
Do the same for the other column and you are done.
Mark as solutions so others can find this easily. 😄
Thanks and regards.
Doing this will duplicate the rows. Eventhough we delete the duplicates, the distribution of the data will not be correct.
@mussaenda thanks for bringing this up. My belief was the rows should've done as a cross join.
Well in this case,
Since the order item and order date is in the same order we have a solution
Note: this will not work for a large data set
First, take a note of the header names then you need to transpose the data. (Button is under transform ribbon)
Since the original data set had two rows here we only have to split the 2 available columns with the comma as a delimiter to the column.
Before:
After:
Then transpose it back to the original state. Update the header names
and do a fill down in the ID column.
This will solve the problem.
Note: if your data set has a large set of rows. This is not the ideal option since you have to split all that columns. If there is a workaround for this problem(split all the columns at once) you can use that as the ideal solution
Proud to be a datanaut.
Again thanks for @mussaenda bringing this up.
regards
Atma.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |