Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Hi @smogie
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.
Hi @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