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

Use Power Query to restructure a wide table into a narrow one grouped by product

Hi,

I'm looking to transform a very wide table (with 490 columns!) into a narrow one. At the moment, my table has four columns of general data, and then 54 columns for each of 9 different products, all side by side. This is a simplistic representation:

From.PNG

Where the different colours show the grouping of three different products:

Products.PNG

My goal is to transform this in Power Query into a table that is 58 columns wide (the four columns of general data and then 54 columns of product data), with each product stacked vertically on top of each other. Again, here is the representation:

To.PNG

I've tried transposing the data, unpivoting and re-pivoting the data and can't get anything to work. I'd be really grateful for any advice the forum may have!

 

Best wishes,

 

Michael

1 ACCEPTED SOLUTION

@Mike86 here it is , you can tweak as you see fit.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
Super User III
Super User III

@Mike86 can you share sample data and that will help to provide the solution.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Appreciate you looking at this. Here's a link to the demo data:

Dropbox File

 

Let me know if this works for you.

 

Thanks,

 

Michael

@Mike86 here it is , you can tweak as you see fit.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Many thanks - a very neat solution. Really appreciate your help!

M

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors