Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everyone,
I have a table with date and time, Status and a flow ID. I want to calculate the duration between the first appearance of the Status 'Delivered' and the Status 'Stop flow'.
I think the first step is to transpose the column, but since there are more statussen with 'Delivered' for one flow ID, I don't know how to do this.
The second step is to calculate the difference as a number between the two new columns and finally create a measure to convert that number to a date and time function. I know those steps, so it's only the first step.
So, the duration between Delivered and Stop flow for the first Flow ID should be 5 minutes and 5 seconds, and for the second Flow ID 5 minutes and 4 seconds.
If necesseary, you can use the link below; it containts the same sample data as in the image.
Solved! Go to Solution.
Let's assume that your oryginal data is named: Sample.
1. Create a new blank query and create an aggregated table per Flow execution id, status with new Columns "min and max".
= Table.Group(Sample, {"Flow execution id", "Status"}, {{"min", each List.Min([Date and time]), type nullable datetime}, {"max", each List.Max([Date and time]), type nullable datetime}})
2. Group it once again packing this information into table.
3. Add custom column with Startdatetime:
List.Min(Table.Column(Table.SelectRows([Combined], each [Status]="Delivered"),"Date and time"))
4. Add custom column with End datetime
List.Max(Table.Column(Table.SelectRows([Combined], each [Status]="Stop flow"),"Date and time"))
5. Add duration
[End datetime] - [Start datetime]
Full code:
let
Source = Table.Group(Sample, {"Flow execution id"}, {{"Combined", each _, type table [Date and time=nullable datetime, Flow execution id=nullable text, Status=nullable text]}}),
#"Added Start Datetime" = Table.AddColumn(Source, "Start datetime", each List.Min(Table.Column(Table.SelectRows([Combined], each [Status]="Delivered"),"Date and time"))),
#"Added End Datetime" = Table.AddColumn(#"Added Start Datetime", "End datetime", each List.Max(Table.Column(Table.SelectRows([Combined], each [Status]="Stop flow"),"Date and time"))),
#"Added Duration" = Table.AddColumn(#"Added End Datetime", "Duration", each [End datetime] - [Start datetime])
in
#"Added Duration"
Proud to be a Super User!
Let's assume that your oryginal data is named: Sample.
1. Create a new blank query and create an aggregated table per Flow execution id, status with new Columns "min and max".
= Table.Group(Sample, {"Flow execution id", "Status"}, {{"min", each List.Min([Date and time]), type nullable datetime}, {"max", each List.Max([Date and time]), type nullable datetime}})
2. Group it once again packing this information into table.
3. Add custom column with Startdatetime:
List.Min(Table.Column(Table.SelectRows([Combined], each [Status]="Delivered"),"Date and time"))
4. Add custom column with End datetime
List.Max(Table.Column(Table.SelectRows([Combined], each [Status]="Stop flow"),"Date and time"))
5. Add duration
[End datetime] - [Start datetime]
Full code:
let
Source = Table.Group(Sample, {"Flow execution id"}, {{"Combined", each _, type table [Date and time=nullable datetime, Flow execution id=nullable text, Status=nullable text]}}),
#"Added Start Datetime" = Table.AddColumn(Source, "Start datetime", each List.Min(Table.Column(Table.SelectRows([Combined], each [Status]="Delivered"),"Date and time"))),
#"Added End Datetime" = Table.AddColumn(#"Added Start Datetime", "End datetime", each List.Max(Table.Column(Table.SelectRows([Combined], each [Status]="Stop flow"),"Date and time"))),
#"Added Duration" = Table.AddColumn(#"Added End Datetime", "Duration", each [End datetime] - [Start datetime])
in
#"Added Duration"
Proud to be a Super User!
@bolfri
Thank you so much!! Works perfectly 🙂 I even have the Duration in a Time format immediatly, which I didn't expect.
If more delivered appear for own ID, do you want to take the earlist event?
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |