cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SimonJF Frequent Visitor
Frequent Visitor

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:

image.png

 

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

image.png

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Group and Transpose a Table

@SimonJF,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
dilumd Established Member
Established Member

Re: Group and Transpose a Table

Hi @SimonJF,

 

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

 

Power Bi Help.JPG

SimonJF Frequent Visitor
Frequent Visitor

Re: Group and Transpose a Table

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.

Moderator v-yuezhe-msft
Moderator

Re: Group and Transpose a Table

@SimonJF,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SimonJF Frequent Visitor
Frequent Visitor

Re: Group and Transpose a Table

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

Highlighted
saketh333 Regular Visitor
Regular Visitor

Re: Group and Transpose a Table

The file is not available any more