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

[Mike86]

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:

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

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:

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

[parry2k]

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

[Mike86]

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

[parry2k · Accepted solution]

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

[Mike86]

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

M