Pivoting a full table for a mailing project

[Spigaw]

Hello dear problem squashers,

I have to solve this situation where I have a classic database with as many rows as yearly situations per registration number. In order to be able to use it with Word for a mailing project, I need to fully pivot it to output a database with one line per registration number.

I have this:

And I need this:

I was able to do it in Excel with INDEX/MATCH, but it was slow and not ergonomic. And anyway, I want to fully automatize it.

When I try to pivot based on the year column in Power Query, it deletes it and does output a lot of null values:

You can find the database (with obviously fake data) via this link.

Thanks in advance for any help!

[AlexisOlson · Accepted solution]

First, unpivot all except the first two columns.

Merge the Year and Attribute into a single column

Then pivot the merged column to get the desired result.

[Spigaw]

I don't have any comment, except that it works flawlessly.

Thank you so much!