Partial Power Query transposition so that captions remain

Question

Novice power query user here. I would greatly appreciate if someone could help me with the following problem. I'm tasked with modifying an excel template that is roughly of that format:

City	Borough	Street	House	Attribute1	Attribute2	Attribute3	Attribute4
New York	Manhattan	W111	102	1	2	3	4
New York	Manhattan	W109	58	11	22	33	44
New York	Brooklyn	E49	56	111	222	333	444
New York	Brooklyn	E49	24	1111	2222	3333	4444

The thing is, it is highly likely that there will be more attributes in the future, so it was decided that we should transpose the template to the following format:

	City	Borough	Street	House	Value
Attribute1	New York	Manhattan	W111	102	1
Attribute1	New York	Manhattan	W109	58	11
Attribute1	New York	Brooklyn	E49	56	111
Attribute1	New York	Brooklyn	E49	24	1111
Attribute2	New York	Manhattan	W111	102	2
Attribute2	New York	Manhattan	W109	58	22
Attribute2	New York	Brooklyn	E49	56	222
Attribute2	New York	Brooklyn	E49	24	2222
Attribute3	New York	Manhattan	W111	102	3
Attribute3	New York	Manhattan	W109	58	33
Attribute3	New York	Brooklyn	E49	56	333
Attribute3	New York	Brooklyn	E49	24	3333
Attribute4	New York	Manhattan	W111	102	4
Attribute4	New York	Manhattan	W109	58	44
Attribute4	New York	Brooklyn	E49	56	444
Attribute4	New York	Brooklyn	E49	24	4444

The idea is that business users will continue to fill it the way they are used to and I should implement a power query script that converts it for our backend convenience and that is not tied to a fixed number of attribute columns.

I watched some YT tutorials and tried removing row with captions (so that the captions don't get transposed alongside with the values) and merging all the attribute columns into one with a delimiter, then unmerging them using that delimiter to rows; this idea seems to have potential: I end up with a single column containing all the values, however what is missing is attribute names and plain values can't be used without names. It would be ideal to have a leftmost column with attribute captions, as in the example. Also I can't manage to force PQ to get all the columns starting with "Attribute1" till the rightmost end, tried to do something with List.Select-ing all columns then excluding the ones that should stay, but I couldn't beat that yet, too.

I would greatly appreciate if anybody could help me with that problem. Thanks in advance!

Answer 1

Load your data into Power Query - Data --> From table/range

Select City, Borough, Street, House, (use Ctrl-left click to select multiple columns), right click and select "Unpivot other columns"

Rearrange the columns if required: