Group and Transpose a Table

[SimonJF]

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.

[v-yuezhe-msft · Accepted solution]

@SimonJF,

Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1Nok3NaWmXqRMpE6DBW

Regards,
Lydia

[Anonymous]

The file is not available any more

[SimonJF]

Lydia, that is fantastic - very efficient way of doing it. Really appreciate your help. Some great techniques in the .pbix you created. Superstar.

[dilumd]

Hi @SimonJF,

I have tired your case with group function in Power BI, just check whether you’re looking for something like this?

[SimonJF]

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.