SOLVED

Issue status log to status on a day

Copper Contributor

I have data log table which i need to present in a status per day. The status log table has the following format:

DateIssueStatus
3-janATodo
15-janAInProgress
20-janAInReview
22-janADone
6-janATodo
18-janAInProgress
28-janAInReview
2-febADone

 

I like to have information how many issues have a certain status on a day: 

DateTodoInProgressInReviewDone
1-jan    
2-jan    
3-jan1   
4-jan1   
5-jan1   
6-jan2   
7-jan2   
8-jan2   
9-jan2   
10-jan2   
11-jan2   
12-jan2   
13-jan2   
14-jan2   
15-jan2   
16-jan11  
17-jan11  
18-jan 2  
19-jan 2  
20-jan 11 
21-jan 11 
22-jan 1 1
23-jan 1 1
24-jan 1 1
25-jan 1 1
26-jan 1 1
27-jan 1 1
28-jan  11
29-jan  11
30-jan  11
31-jan  11
1-feb  11
2-feb   2
3-feb   2
4-feb   2

 

The information statuslog information comes from a database. I am able to load it into Excel. The period which I like to analyze is not flexible.

 

I prefer to use pivot tables for this, but I am not able to load the information from the status log table  direct into the pivot table.

 

Any suggestion?

 

 

5 Replies

That could be done with Power Query, please see the mockup attached.

Assumptions are

- first date in the log is with zeros (could be corrected)

- status is changed if the number for next date is more than for previous

Thanks for your quick response, in the attached I see that the second table is transferred into the first status log table. But I need the have this the other way around.

 

I have a status log table and need to transfer it into the number of statusses per day table. Is this  also possible with power query?

Oops, sorry for misunderstanding. Okay, I'll try

best response confirmed by Joko2611 (Copper Contributor)
Solution

Here is another attempt. To generate the log separate Calendar table is created with first date and number of dates as parameter. Status table is merged with it and log for each code is generated by code inside. Not sure that's optimal from performance point of view and if I understood all logic of log correctly, but that's first what was in mind.

Please check attached file.

Thanks, this makes sense.

1 best response

Accepted Solutions
best response confirmed by Joko2611 (Copper Contributor)
Solution

Here is another attempt. To generate the log separate Calendar table is created with first date and number of dates as parameter. Status table is merged with it and log for each code is generated by code inside. Not sure that's optimal from performance point of view and if I understood all logic of log correctly, but that's first what was in mind.

Please check attached file.

View solution in original post