need help on how to substract rows max - min in column TerminatedDate that fullfill some criteria.

Hi Anonymous  

please paste this code into the advanced editor and follow the steps:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZU5bsMwEEWvEqg2YM7GrXQOkAMYLpyki5E08f1DUXDD4QjjdMKHnt6I+CTP5wUDF2EBSstheb3ebqfrx1d7BDoGPGLA8AJSuQxRrCLL5TDwb/ff95/796fiBWaRl+8yyy8hh9I+IPb8qcLIp4qkecPv5g1/rhRUxOz2Nx4tngOlDAWY9vyo/USaN/2o/e2XnH4MNUQVUfb6/bzpZ1GRTOY3/RO+bYnOQ6QgGQvb6x8rRdUfjpo3+9+nU5GXT2r9tsg7fyu77j9N/t/0o/Y/+uPj0yza+CwJAkXY7X8eolJhwpv9F55FXr7LPH6zv8A6Eq9/e9ngBbhkwshxr7849q8diaR5s784rl+P/P5x/61ResI/4SU/w4/9jY/92/kEEfbXb7w/03okKP4f95/Hn6bnT3DzRfVvjfzz95c9vH1/oIpgMr99f2i+RZfLHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RemoteID = _t, MediaType_name = _t, ConnectedDate = _t, TerminatedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RemoteID", Int64.Type}, {"MediaType_name", type text}, {"ConnectedDate", type datetime}, {"TerminatedDate", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"RemoteID"}, {{"CallBacks", each Table.SelectRows(_, each [MediaType_name] = "CallBack")}, {"Outbounds", each Table.SelectRows(_, each [MediaType_name] = "Outbound")}}),
    #"Expanded CallBacks" = Table.ExpandTableColumn(#"Grouped Rows", "CallBacks", {"MediaType_name", "ConnectedDate", "TerminatedDate"}, {"MediaType_name", "ConnectedDate", "TerminatedDate"}),
    DiffTerminatedDate = Table.AddColumn(#"Expanded CallBacks", "Diff-TerminatedDate", each [TerminatedDate] - List.Max([Outbounds][TerminatedDate])),
    #"Added Custom" = Table.AddColumn(DiffTerminatedDate, "Diff-ConnectedDate", each [ConnectedDate] - List.Min([Outbounds][ConnectedDate]))
in
    #"Added Custom"

 

The reason for asking for sample data is that seeing the desired result helps understanding the explanations for the problem/requirements. And prevents silly code, as one can detect small errors or typos in the description.

This was missing from what you've sent so I have not been able to check if the negative amounts are really correct here.