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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors