Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Spigaw
Helper II
Helper II

Pivoting a full table for a mailing project

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:

Spigaw_1-1638289663967.png

And I need this:

Spigaw_2-1638289676702.png

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:

Spigaw_3-1638289817494.png

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

 

Thanks in advance for any help!

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

First, unpivot all except the first two columns.

AlexisOlson_0-1638292250009.png

Merge the Year and Attribute into a single column

AlexisOlson_1-1638292314488.png

Then pivot the merged column to get the desired result.

 

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

First, unpivot all except the first two columns.

AlexisOlson_0-1638292250009.png

Merge the Year and Attribute into a single column

AlexisOlson_1-1638292314488.png

Then pivot the merged column to get the desired result.

 

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

Thank you so much!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors