Stuck on Transforming Data via Pivot in PowerQuery Editor

[Anonymous]

Hi Folks,

I'm in a pickle. I am trying to get my data set in Power BI in Power Query editor to go from this below:

Employee ID	Worker	Location Address - Country	Worker Type	Time Off	Balance
123456	Scooby Doo	USA	Employee	PTO	10
123456	Scooby Doo	USA	Employee	Sick	2
123456	Scooby Doo	USA	Employee	Medical	5
123456	Scooby Doo	USA	Employee	Holiday	0
123456	Scooby Doo	USA	Employee	Jury	8
123456	Scooby Doo	USA	Employee	Marriage	0

To this right here -- I want to take the time off plan and switch them to one row with the balances like this:

Employee ID	Worker	Location Address - Country	Worker Type	PTO	Sick	Medical	Holiday	Jury	Marriage
123456	Scooby Doo	USA	Employee	10	2	5	0	8	0

I've been trying to do this for the last hour and I can't seem to find a good example on how to do this. Any help would be much appreciated please! Thanks everyone! 

Best,

AJ

[HotChilli · Accepted solution]

Select the TimeOff column and choose 'Pivot' from the Transform menu.  Use Balance as the values.

[Anonymous]

I did that. While it gives me the columns, it puts the balance on different rows instead of on the same row. 😞

[HotChilli]

Post the advanced editor code and i'll have a look.

Are you using the sample data or your real data?

[Anonymous]

Actually, it worked. There was another column that I didn't include that kept it from truncating.

Thank you -- this worked! Much appreciated!

[Anonymous]

It gives me an output like this:

Employee ID	Worker	Location Address - Country	Worker Type	PTO	Sick	Medical	Holiday	Jury	Marriage
123456	Scooby Doo	USA	Employee	10
123456	Scooby Doo	USA	Employee		2
123456	Scooby Doo	USA	Employee			5
123456	Scooby Doo	USA	Employee				0
123456	Scooby Doo	USA	Employee					8
123456	Scooby Doo	USA	Employee						0