Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 @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.
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