Unpivoting columns in pairs

Morning everyone,

I have a table with the following headings:

Date | Time | Class | Type | Value |Type2 | Value2 | Type3 | Value3

What I need is to restructure the table into the form

Date | Time | Class | Type | Value

I'm currently using Excel 2013 and I've been messing around with Power Query in order to get the output I need, so far no luck. Can anyone help me please? Ideally with a VBA solution?

Many thanks

Can be done easily with PQ - provide a sample workbook and I'll show you how.

The way I'd do it... Using PowerQuery.
Make 3 separate queries to source data.
In each query remove all but 1 Type/Value pair (each retaining different pair).
Rename each Type/Value pair to have same name.
Pick 1 query, and append other 2 to it.

If using VBA... there are many ways to do it.
1. Use ADO to query and use SQL statement to transform data (i.e. same concept as PQ, but using UNION ALL).
2. Put range into array. Create empty array with row count x 3 of original, and 5 columns. Loop through original array and fill the empty array.
etc etc.

See attached for sample.

OK - here's the M code to do what you want in PQ:

Please Login or Register  to view this content.

Make 3 separate queries to source data.

No need for that, really, although it gets the result.

You're welcome!

PQ or VBA?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.