Forum Discussion

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 ...

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.

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

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