Forum Discussion
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 ...
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"
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
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"
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