cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mike86
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
parry2k
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.





Mike86
Regular Visitor

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

Mike86
Regular Visitor

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

M

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.