Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Folks,
I'm really keen to figure out an efficient way to solve the following data transformation challenge.
I have a monthly report which provides Employment Status (NEW or EW) for a list of Employees structured like this:
1. My first requirement is to determine which employees have changed status from NEW to EW
2. My second requirement is to count how many months they've been "EW" for.
The table below shows the transformation required (req 1), with the last column containing the count of months, as per req 2 above
I can achieve this, but in a very convoluted way (e.g. searching and replacing NEW and EW with zeros and ones, then pivoting, then replacing back the values). There must be a better way! Any help much appreciated.
Solved! Go to Solution.
@SimonJF,
Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1Nok3NaWmXqRMpE6DBW
Regards,
Lydia
@SimonJF,
Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1Nok3NaWmXqRMpE6DBW
Regards,
Lydia
The file is not available any more
Lydia, that is fantastic - very efficient way of doing it. Really appreciate your help. Some great techniques in the .pbix you created. Superstar.
Hi @SimonJF,
I have tired your case with group function in Power BI, just check whether you’re looking for something like this?
Thanks Dilumd for taking the time.
Unfortunately that doesn't get me the time series table with Month as columns and a unique row of employee name where they have converted from NEW to EW.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |