- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Partially syndicated - Outbound Partially syndicated - Outbound](/html/assets/ics-partial-syndication-img.png)
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.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",{{"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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
Sorry this has not resolved the requirments. thanks for trying
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
Can you please check again? I think my version produces the expected outcome.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.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",{{"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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![Syndicated - Outbound Syndicated - Outbound](/html/assets/ics-fully-syndication-img.png)
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](/skins/images/2886330F5950BA2E79FCBC60F4CB919C/responsive_peak/images/icon_anonymous_message.png)
Helpful resources
Subject | Author | Posted | |
---|---|---|---|
09-04-2024 04:48 AM | |||
12-06-2023 03:31 PM | |||
03-21-2023 08:53 PM | |||
04-26-2024 09:27 AM | |||
02-06-2023 07:02 PM |