Forum Discussion

FredPereira

Frequent Visitor

6 years ago

Solved

Calculate difference between the first two dates in rows using M

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!

Need Help

dax

6 years ago

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"

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.

5 Replies

Greg_Deckler
Community Champion
6 years ago
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
  Frequent Visitor
  6 years ago
  MINX in PowerQuery?

FredPereira

Frequent Visitor

6 years ago

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

Community Support

6 years ago

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"

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
Frequent Visitor
6 years ago
This works! Thank you very much!

Forum Discussion

Calculate difference between the first two dates in rows using M

5 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

1. How to add two different calculated columns of two different table

Calculating Differences

Calculating day/time difference