Reply
cottrera
Post Prodigy
Post Prodigy
Partially syndicated - Outbound

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

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

1 ACCEPTED SOLUTION

Syndicated - Outbound

 

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"

 

View solution in original post

8 REPLIES 8
lbendlin
Super User
Super User

Syndicated - Outbound
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.

Syndicated - Outbound

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

 

 

 

Syndicated - Outbound

Like this?

 

lbendlin_0-1693862547233.png

 

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"

Syndicated - Outbound

Sorry this has not resolved the requirments.  thanks for trying

Syndicated - Outbound

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

Syndicated - Outbound

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 NumberJob Status DescriptionMin DateAddedMax DateAdded
8241424Awaiting Scheduling03/01/202309/01/2023
8241424Job Scheduled01/02/202331/01/2023

How ever I would expect to see this result instead

Job NumberJob Status DescriptionMin DateAddedMax DateAdded
8242000Awaiting Scheduling04/01/202311/01/2023
8242000Job Scheduled12/01/202301/02/2023
8242000Awaiting Scheduling02/02/202316/02/2023
8242000Job Scheduled17/02/202315/03/2023
8242000Awaiting Scheduling16/03/202329/03/2023
8242000job Scheduled30/03/2023

12/04/2023

 

 

thanks Richard

 

Syndicated - Outbound

 

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"

 

Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)