Forum Discussion

SimonJF's avatar
SimonJF
Frequent Visitor
8 years ago
Solved

Group and Transpose a Table

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.

5 Replies

  • dilumd's avatar
    dilumd
    Impactful Individual
    8 years ago

    Hi SimonJF,

     

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

     

    • SimonJF's avatar
      SimonJF
      Frequent Visitor
      8 years ago

      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.

    • SimonJF's avatar
      SimonJF
      Frequent Visitor
      8 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      The file is not available any more