Forum Discussion
HR report from Excel to PowerBI
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
2 Replies
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.
LinkedInHi Mariusz ,
That was the way I was using before. Now I'm looking for a way to get the same results without importing column E - R...
Gr. Steven
