Transforming Columns with comas to separate rows

[Misq]

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!

[Misq · Accepted solution]

Yes this dosent work for my dataset ~40k rows, but ..... here is a good solution!

https://www.youtube.com/watch?v=JMOnr3DOqyk

[Dhacd]

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.

[Misq]

Hi Shaved,

Can it be a simple excel file or pbix? I will definitely post it 😉

[Dhacd]

Just copy the cells from A1 to C3 from the excel to the reply box and I will take it from there 😄

[Misq]

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

[Dhacd]

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.

[mussaenda]

Doing this will duplicate the rows. Eventhough we delete the duplicates, the distribution of the data will not be correct.

[Dhacd]

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