HR report from Excel to PowerBI

Anonymous · ‎06-11-2020

Hi all,

I'm 'rebuilding' an Excel report into PowerBI but I'm struggling with the following: HR wants to count the amout of people per month and year that are on contract and the amount of people that get a promotion. Right now I have this overview with formulas:

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
Pers.nr.	Startdate contract	Enddate contract	Title	Same pers.nr?	Promotion?	jan-20	feb-20	mrt-20	apr-20	mei-20	jun-20	jul-20	aug-20	sep-20	okt-20	nov-20	dec-20
15	01-01-2020	30-04-2020	Trainee			1	1	1	1	0	0	0	0	0	0	0	0
15	01-05-2020		Consultant	Yes	Yes	0	0	0	0	1	1	1	1	1	1	1	1
16	01-01-2020	31-05-2020	Consultant			1	1	1	1	1	0	0	0	0	0	0	0
16	01-06-2020		Consultant	Yes		0	0	0	0	0	1	1	1	1	1	1	1

The columns A - D don't contain formulas, this comes right from the source. The rest of the colums do..

E2: IF(A2=A1;"Yes";"")

F2: IF(AND(A2=A1;E2="Yes";D2<>D1);"Yes";"")

G2: IF(AND($B2<=G$1;OR($C2="";$C2>=G$1));1;0) <-- pulled from G2 - R2

I need to get rid of the formulas in the source en 'rebuild' them in PowerBI. So I only have column A - D to rebuild the same result in PowerBI... Hopefully someone can help me with this (for me) braincracker!

Kind regards,

Steven

Mariusz · ‎06-11-2020

Hi @Anonymous

You can select all the non Year Month columns and click on the small arrow down next to Unpivot Columns and chose Unpivoted Other Columns option.

this will transform your data so it looks like below, and its easier to aggregate and split month and year.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn