Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
3 years ago
Solved

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

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

     

    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"

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago
    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's avatar
      cottrera
      Post Prodigy
      2 years ago

      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.com)

      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's avatar
        lbendlin
        Super User
        2 years ago

        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"