Identifying last time a person did a task

[Veyron]

Hi everyone,

I need to find out when was the last time a person did a task.
It sounds easy but due to the way the data is displayed, I am unable to sort it out.

Here you have an example of it:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]23/09[/TD]
[TD]24/09[/TD]
[TD]25/09[/TD]
[TD]26/09[/TD]
[TD]27/09[/TD]
[TD]28/09[/TD]
[TD]29/09[/TD]
[/TR]
[TR]
[TD]Worker 1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Worker 2[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Worker 3[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Worker 4[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]









Solution would be:

Worker 1
Task A: 29/09
Task B: 25/09
Task C: 27/09
Task D: 28/09

And so on...

Thanks for your help!

 

[jasonb75]

With power query it can be done by unpivoting all of the date columns, which will give 3 columns, name, date and task as an intermediate step.
Then pivot the task column using the date column for values and setting the aggregation to max.

The results come out as below.

Name	        A       B       C        D
Worker 1	29-Sep	25-Sep	27-Sep	28-Sep
Worker 2	28-Sep	29-Sep	25-Sep	27-Sep
Worker 3	28-Sep	29-Sep	25-Sep	27-Sep
Worker 4	29-Sep	28-Sep	25-Sep	26-Sep

Is that something that you can work with?

 

[Veyron]

With power query it can be done by unpivoting all of the date columns, which will give 3 columns, name, date and task as an intermediate step.
Then pivot the task column using the date column for values and setting the aggregation to max.

The results come out as below.

Name            A       B       C        D
Worker 1    29-Sep    25-Sep    27-Sep    28-Sep
Worker 2    28-Sep    29-Sep    25-Sep    27-Sep
Worker 3    28-Sep    29-Sep    25-Sep    27-Sep
Worker 4    29-Sep    28-Sep    25-Sep    26-Sep

Is that something that you can work with?

Definitely. I just need to know now how to use Power Query
I will look for some tutorials.

Many thanks Jason!

 

[Veyron]

With power query it can be done by unpivoting all of the date columns, which will give 3 columns, name, date and task as an intermediate step.
Then pivot the task column using the date column for values and setting the aggregation to max.

Just to let you know that your solution works.

thanks mate