Forum Discussion
M code to capture status changes and #days before status changes again
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"
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.
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. Ithank you RIchard
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"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 resultJob 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
