Transpose and Unpivot?

Power Query
1

Hi All,
I am a relative beginner with Power Query and the M language. I am running into issues using transform and unpivot: not sure they are even the right steps to be using. I have attached the excel file I am using as the data source, which also includes a tab titled desired structure, showing the ideal end result. I have also attached the pbix file for reference.

Thank you for your help.

Eric

Payroll.pbix (25.4 KB) Payroll.xlsx (11.4 KB)

2

Hi @eric_m,

Here’s what I did.

Removed the top 2 rows and promote headers

image
image852Ă—101 4.75 KB

From the Source, kept the top 2 rows, removed other columns and promoted headers
image

Added a custom column and called the #“Removed Top Rows” step
image

Expanded the nested table and changed type

image
image1167Ă—101 5.54 KB

Here’s your sample file Payroll.pbix (50.7 KB)
I hope this is helpful

1 Like
3

Melissa,

Many thanks, this is exactly what I was trying to do. Did you perform these steps using the PQ editor tool bar or by writing M? Just wondering for my own education.

Thanks again for your help.

Eric

4

I mix and match most of the time, so I did a bit of both…
But you can do this through the UI for 98% Except for calling the Source a second time and the #“Removed Top Rows” step in the Custom Column there’s no need to write any M