Table Manipulation

[lbird2]

Info1	Info2	Info3	Info4	Data1	Data2	Data3	Data4
A	1	Blue	Hot	0.4	0.5	1.3	2.7
B	2	White	Cold	2.3	0.1	1.5	8.1
C	3	Gold	Hot	2.6	7.1	6.1	0.8
D	4	Brown	Cold	0.9	2.6	2.3	1.1
E	1	White	Hot	0.1	0.6	2.3	2.0
F	7	Red	Cold	5.2	1.0	8.1	5.1
G	2	Purple	Cold	1.6	0.1	4.0	3.5

<tbody>
</tbody>

I find I need to do this often so I'm looking for a way to automate. I would like to repeat the "Info" rows and move each of the "Data" values to a single column of data. The table below shows what the result would be for the first two "Data" columns.

I get data in this format often and find the converted table to be much more flexible for analysis.

To make matters harder, the number of Info and Data items vary so I would like to account for that.

Any help would be greatly appreciated and would make my life a lot less tedious.

Info1	Info2	Info3	Info4	Data
A	1	Blue	Hot	0.4
B	2	White	Cold	2.3
C	3	Gold	Hot	2.6
D	4	Brown	Cold	0.9
E	1	White	Hot	0.1
F	7	Red	Cold	5.2
G	2	Purple	Cold	1.6
A	1	Blue	Hot	0.5
B	2	White	Cold	0.1
C	3	Gold	Hot	7.1
D	4	Brown	Cold	2.6
E	1	White	Hot	0.6
F	7	Red	Cold	1.0
G	2	Purple	Cold	0.1

<tbody>
</tbody>

 

[lbird2]

Thanks Jan. This worked nicely. Thanks also for the Get-Transform tip - I'm loving that tool!

 

[SpillerBD]

Thanks Jan. This worked nicely. Thanks also for the Get-Transform tip - I'm loving that tool!

Awesome! Glad you have the tool available.
For those still tied to (or trapped) to a version where they can't use the PowerBI tool set, a macro specific to their case is great. Alternatively for those without access to the Get-Transform (PowerQuery) there is a technique using the PivotTable Wizard and multiple consolidation ranges.
(Trouttrap2 gives a brief description in this forum thread. though he doesn't mention the concatenate then Text-to-Columns of the "Row" combinations.)