cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Misq
Frequent Visitor

Transforming Columns with comas to separate rows

Hi all,
Is it possible to transform data from green table (image below) so they will be looking as data in the blue one?

Misq_0-1654780456288.png

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!

 

1 ACCEPTED SOLUTION
Misq
Frequent Visitor

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

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

View solution in original post

8 REPLIES 8
Misq
Frequent Visitor

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

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

Dhacd
Resolver III
Resolver III

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

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 😄

Misq
Frequent Visitor

So, here it is:

IDOrderdItemOrderDates
11,2,22020-01-01, 2020-02-01, 2020-03-01
21,1,3,32020-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

Dhacd_0-1654785582369.png

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.

Dhacd_1-1654785707835.png

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:

Dhacd_1-1654832322228.png

 


After:

Dhacd_0-1654832294613.png


Then transpose it back to the original state. Update the header names 
and do a fill down in the ID column.

Dhacd_2-1654832366567.png

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.