1

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!

Improve this question

1 Answer 1

Reset to default
2

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

enter image description here

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

enter image description here

Rearrange the columns if required:

enter image description here

Improve this answer
2
  • Michael, that turned out to be really easy, thank you very much and please excuse my noob question.
    – Alexander
    Commented Jul 9 at 19:32
  • 1
    @Alexander Welcome, no reason to apologise. We all learn. I have asked much simpler questions in the past.
    – Michal
    Commented Jul 9 at 19:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.