M code to capture status changes and #days before status changes again

[cottrera]

Hi Community, bit of a tricky request but here goes. 

I have added a link to my data set.
https://github.com/Cottera/Sample-Data/blob/main/Status%20changes%20over%20time.csv  
This example is for one 'job number' however the master data set has many job numbers. So the solution with have to factor this in.

Each row of the data represents a new day. This is captured via the 'DateAdd' column. You will notice that from time to time the 

'Job Status' also changes. 

I am looking for an M codethat looks for the start 'DateAdd' and end 'DateAdd' every time a status changes.

In this example of which is a summarised version of the attached data set I have added two colums at the end to represent when the status change started and finished. The '# Days job was in this status' used the 'AgeOfJobInWorkingDays' data

DateADDED	Date Logged	AgeOfJobInWorkingDays	Date Completed	WorkingDaysToComplete	Job Description	Awaiting Scheduling	Job Scheduled	Status Change Start Finish	# Days job was in this status
13/01/2023	12/01/2023	1	04/08/2023	141	Routine Scope - External Works		1	Start
10/02/2023	12/01/2023	21	04/08/2023	141	Routine Scope - External Works		1	Finish	21
11/02/2023	12/01/2023	21	04/08/2023	141	Routine Scope - External Works	1		Start
13/02/2023	12/01/2023	22	04/08/2023	141	Routine Scope - External Works	1		Finish	1
14/02/2023	12/01/2023	23	04/08/2023	141	*Roof repair*		1	Start
17/05/2023	12/01/2023	85	04/08/2023	141	*Roof repair*		1	Finish	67
18/05/2023	12/01/2023	86	04/08/2023	141	*Roof repair*	1		Start
22/05/2023	12/01/2023	88	04/08/2023	141	*Roof repair*	1		Finish	3
23/05/2023	12/01/2023	89	04/08/2023	141	*Roof repair*		1	Start
04/08/2023	12/01/2023	141	04/08/2023	141	*Roof repair*		1	Finish	54

The code would also need to use the very first 'DateAdd' as the main starting point.

Here is the link to my M code so far.

Sample-Data/Status changes over time - M Code so far.txt at main · Cottera/Sample-Data (github.com)

thank you 

RIchard

[lbendlin]

This example is for one 'job number' however the master data set has many job numbers. So the solution with have to factor this in.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[cottrera]

Hi Ibendlin

thnak you for responding. I have uploaded a slightly bigger sample data se with multiple job numbers here
Sample-Data/Status changes over time multiple job numbers.csv at main · Cottera/Sample-Data (github....

On this tab  'Status changes over time multip' I hve added two extra fields that I invisage being added to the data set in power query (you know beter than me) 
The fields are,
Status change start or finish - this shows when the status started and when if finshed.
Working Days between statuses - this shows the workign days the job remainded at any of these statuses. It used another field AgeOfJobInWorkingDays to obtain this information.  I

thank you RIchard

[lbendlin]

Like this?

let
    Source = Csv.Document(File.Contents("C:\Users\xxx\Downloads\Status changes over time multiple job numbers.csv"),[Delimiter=",", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job Number", Int64.Type}, {"DateADDED", type text}, {"Date Logged", type date}, {"AgeOfJobInWorkingDays", Int64.Type}, {"Date Completed", type text}, {"WorkingDaysToComplete", Int64.Type}, {"Job Description", type text}, {"Job Status Description", type text}, {"Status change start or finish", type text}, {"Working Days between statuses", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Job Number", "Job Status Description"}, {{"Min DateAdded", each List.Min([DateADDED]), type nullable text}, {"Max DateAdded", each List.Max([DateADDED]), type nullable text}})
in
    #"Grouped Rows"

[cottrera]

Sorry this has not resolved the requirments.  thanks for trying

[lbendlin]

Can you please check again? I think my version produces the expected outcome.

[cottrera]

Hi Ibendlin  than you for your M-Code. It seems to we going n the right direction , however the code only seems allow for a maximum of 2 status chages.

For example for this job number your code produces this result

Job Number	Job Status Description	Min DateAdded	Max DateAdded
8241424	Awaiting Scheduling	03/01/2023	09/01/2023
8241424	Job Scheduled	01/02/2023	31/01/2023

How ever I would expect to see this result instead

Job Number	Job Status Description	Min DateAdded	Max DateAdded
8242000	Awaiting Scheduling	04/01/2023	11/01/2023
8242000	Job Scheduled	12/01/2023	01/02/2023
8242000	Awaiting Scheduling	02/02/2023	16/02/2023
8242000	Job Scheduled	17/02/2023	15/03/2023
8242000	Awaiting Scheduling	16/03/2023	29/03/2023
8242000	job Scheduled	30/03/2023	12/04/2023

thanks Richard

[lbendlin · Accepted solution]

let
    Source = Csv.Document(File.Contents("C:\Users\xxx\Downloads\Status changes over time multiple job numbers.csv"),[Delimiter=",", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"DateADDED", type date}},"en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Job Number", "Job Status Description"}, {{"Min DateAdded", each List.Min([DateADDED]), type nullable text}, {"Max DateAdded", each List.Max([DateADDED]), type nullable text}},GroupKind.Local)
in
    #"Grouped Rows"

[cottrera]

My appologies I still had the forst code in my note pad and not the new code. This works fine. thank you for your patience Richard