Calculate difference between the first two dates in rows using M

[FredPereira]

Hi,

First of all, thanks for your time! Your help is VERY appreciated.

Let's say I have the following table:

Case Number	Email Date/Time	IsIncoming
0000000	1-1-20 14:00	FALSE
1111111	1-2-20 14:00	TRUE
1111111	1-3-20 14:00	FALSE
2222222	1-4-20 14:00	TRUE
2222222	1-5-20 14:00	FALSE
3333333	1-6-20 14:00	TRUE
3333333	1-7-20 14:00	FALSE
3333333	1-8-20 14:00	TRUE
3333333	1-9-20 14:00	FALSE

Each row represents an e-mail interaction, either incoming or not. To each email that I receive in a given case number, I need to find if the first response was within 24 hours. Regardless of how many interactions I have, I only need to know the hours that went between receiving that first email (IsIncoming=TRUE) and the first response (IsIncoming=FALSE).

In DAX this would be manageable to calculate with variables (the other option would be to use an aggregated table - this would be relatively straight-forward in SQL), however, I "need" to do this in PowerQuery, with which I am not very familiar besides querying by clicking.

I know that I can brute force my way with merge queries by, for instance, calculating an index column grouped by case number and then creating an aggreagated table with the correspondent dates (and then calculating the difference between dates of indexes 1 and 2 in each case if index 1 equals TRUE), but I believe there might be more elegant ways to calculate which cases were responded within SLA using M.

Any help? Either by adding a calculated column flagging cases within and out of SLA or separate table.

Again, thank you!

Kind regards!

[FredPereira]

I believe I solved it...

let
    Source = Excel.Workbook(File.Contents("C:\Users\XXXXX\Desktop\XXXXXX.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Sorted Rows" = Table.Sort(Table1_Table,{{"Email Date/Time", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Case Number"}, {{"Rows", each _, type table [Case Number=text, #"Email Date/Time"=datetime, IsIncoming=logical]}}),
    #"Indexed" = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(Indexed, "Rows", {"Email Date/Time", "IsIncoming", "GroupIndex"}, {"Rows.Email Date/Time", "Rows.IsIncoming", "Rows.GroupIndex"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Rows",{{"Case Number", type text}, {"Rows.Email Date/Time", type datetime}, {"Rows.IsIncoming", type text}, {"Rows.GroupIndex", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Rows.Email Date/Time", "Date"}, {"Rows.GroupIndex", "GroupIndex"}, {"Rows.IsIncoming", "IsIncoming"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
    #"IsSLA" = Table.AddColumn(#"Added Index", "IsSLA", each if [GroupIndex]=2 and #"Added Index"[IsIncoming]{[Index]-1} = "true" then if Duration.TotalHours(#"Added Index"[Date]{[Index]}-#"Added Index"[Date]{[Index]-1}) <=24 then 1 else 0 else null, type number)
in
    #"IsSLA"

Any other suggestions?

[dax · Accepted solution]

Hi @FredPereira ,

You could refer to below M code to see whether it work or not.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMoAAJR0lQ11DXSMDBUMTKzDXzdEn2FUpVidayRACwEqMkJWEBIViqjDGbogRBICVmGA1BFmFKXZDjCEArMQMqyHIKswJG2JB0BBLLIbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Number" = _t, #"Email Date/Time" = _t, IsIncoming = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Number", type text}, {"Email Date/Time", type datetime}, {"IsIncoming", type logical}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Case Number", "IsIncoming"}, {{"min", each List.Min([#"Email Date/Time"]), type datetime}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"IsIncoming", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"IsIncoming", type text}}, "en-US")[IsIncoming]), "IsIncoming", "min"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each [false]-[true]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"false", "true"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Case Number"}, #"Pivoted Column", {"Case Number"}, "Removed Columns", JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"false", "true"}, {"false", "true"})
in
    #"Expanded Removed Columns"

Or 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMoAAJR0lQ11DXSMDBUMTKzDXzdEn2FUpVidayRACwEqMkJWEBIViqjDGbogRBICVmGA1BFmFKXZDjCEArMQMqyHIKswJG2JB0BBLLIbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Number" = _t, #"Email Date/Time" = _t, IsIncoming = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Number", type text}, {"Email Date/Time", type datetime}, {"IsIncoming", type logical}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Case Number", "IsIncoming"}, {{"min", each List.Min([#"Email Date/Time"]), type datetime}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"IsIncoming", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"IsIncoming", type text}}, "en-US")[IsIncoming]), "IsIncoming", "min"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each Duration.Days(Duration.From([false]-[true]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"false", "true"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Case Number"}, #"Changed Type", {"Case Number"}, "Removed Columns", JoinKind.LeftOuter),
    #"Expanded Removed Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Email Date/Time", "IsIncoming"}, {"Email Date/Time", "IsIncoming"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Removed Columns1",null,0,Replacer.ReplaceValue,{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Custom.1", each if [Custom] <= 1 then "Y" else "N")
in
    #"Added Custom1"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FredPereira]

This works! Thank you very much!

[Greg_Deckler]

Seems like you could do a MINX for Date/Time on a filter of IsIncoming TRUE to get your first arrival date/time. Then a MINX for Date/Time on a filter of IsIncoming FALSE.

[FredPereira]

MINX in PowerQuery?