Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I would like to get some help from the experts on how to do below in M
I have offers that get passed through an approval tool, generating events:
I would need to calculate the duration between teh submision ( action) and the subsequent approval/rejection ( recation)
Issue ( for me) is that an offer can be reviseed muktiple times so I want find out in M how I can connect the reaction to the correct previous action in order o calculate whether the SLA of the approval was met
Thank you for the guidance
Herewith some sample data
ID | Event_owner | Event | Event_datetime |
12 | Tim | Submit | 14/05/2020 14:49:10 |
12 | Gerry | Approve | 14/05/2020 14:52:22 |
12 | Tim | Revise | 14/05/2020 14:55:25 |
12 | Tim | Submit | 14/05/2020 14:57:04 |
12 | Lindsay | Approve | 14/05/2020 14:57:33 |
12 | Tim | Revise | 14/05/2020 15:00:00 |
12 | Tim | Submit | 14/05/2020 15:00:45 |
12 | Gerry | Approve | 14/05/2020 15:03:03 |
70 | John | Submit | 26/10/2020 12:33:52 |
70 | Gerry | Reject | 26/10/2020 14:07:43 |
70 | John | Revise | 26/10/2020 14:10:08 |
70 | John | Submit | 26/10/2020 14:13:35 |
70 | Gerry | Approve | 26/10/2020 14:16:54 |
thanks
Solved! Go to Solution.
@Kath_BE ,
You can create a index column and a custom column like:
try
let
_row = #"Added Index"{[Index] - 1},
_ID = [ID],
_Event_datetime = [Event_datetime]
in
if _row[ID] = _ID then
Duration.TotalMinutes(_Event_datetime - _row[Event_datetime])
else
null otherwise null
Hello @Kath_BE
you can add a index-column and a new column. This approach assumes that on every submit-event a Approve or a reject-event happens
Here an example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdFNC8IwDAbgvyI7D5amzaa5eRLE0/Q2dvCj4IR9sOnAf2+YzsqmMihtoQ/hTZoknkLP93ZZLvv2dsizq1yUCYACBITZTBk2C1bgpf4Lr2xd3+VcVlVdtnbsCRnR+Wfx2LZZ8w0TIw3xryQUMRiHN1lxavb/s0Ss9bQsxACypmXpsKHJUxGvZXU+Anldl+fiszqGgYJeo4SWMTrdV4/txR5H3DBEbMbF330OtJI+5xOjiNasaRzFNTr0IZN8UvoA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Event_owner = _t, Event = _t, Event_datetime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Event_owner", type text}, {"Event", type text}, {"Event_datetime", type datetime}}, "de-DE"),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
AddDuration = Table.AddColumn
(
#"Added Index",
"Duration minutes submit - approve ",
(row)=> if row[Event]="Approve" or row[Event]="Reject" then Duration.TotalMinutes(row[#"Event_datetime"]- #"Added Index"{[Index= row[Index]-1]}[#"Event_datetime"]) else "",
type number
)
in
AddDuration
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Kath_BE
you can add a index-column and a new column. This approach assumes that on every submit-event a Approve or a reject-event happens
Here an example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdFNC8IwDAbgvyI7D5amzaa5eRLE0/Q2dvCj4IR9sOnAf2+YzsqmMihtoQ/hTZoknkLP93ZZLvv2dsizq1yUCYACBITZTBk2C1bgpf4Lr2xd3+VcVlVdtnbsCRnR+Wfx2LZZ8w0TIw3xryQUMRiHN1lxavb/s0Ss9bQsxACypmXpsKHJUxGvZXU+Anldl+fiszqGgYJeo4SWMTrdV4/txR5H3DBEbMbF330OtJI+5xOjiNasaRzFNTr0IZN8UvoA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Event_owner = _t, Event = _t, Event_datetime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Event_owner", type text}, {"Event", type text}, {"Event_datetime", type datetime}}, "de-DE"),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
AddDuration = Table.AddColumn
(
#"Added Index",
"Duration minutes submit - approve ",
(row)=> if row[Event]="Approve" or row[Event]="Reject" then Duration.TotalMinutes(row[#"Event_datetime"]- #"Added Index"{[Index= row[Index]-1]}[#"Event_datetime"]) else "",
type number
)
in
AddDuration
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
@Kath_BE ,
You can create a index column and a custom column like:
try
let
_row = #"Added Index"{[Index] - 1},
_ID = [ID],
_Event_datetime = [Event_datetime]
in
if _row[ID] = _ID then
Duration.TotalMinutes(_Event_datetime - _row[Event_datetime])
else
null otherwise null
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.