Iterated time difference based on a group of unique IDs

Hi Dalton948 ,

 

Thanks for reaching out to our community.

For your first requirement about sorting them on NextReplyTime group by ID, you can sort twice in order, as shown below, first click on the ID column to sort in ascending order, and then click on the NextReplyTime column to sort in ascending order. In the end, you can see that they sort the NextReplyTime columns by ID grouping, and you can also see that there are sorted sequences 1 and 2 next to the field name.

Your second requirement is that you want to get the value from previous row in groups. Here're to methods.

Method1:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZVLT9tAFIX/yigrkJC4r3nuopQFi1SRqNoFsAjgIquJIzkpEv++11gxngGkvrwdHc/5fM/x9fX1DD0jeGBG8mF2Nlu1u6f6oWrNt/Vhv2vMYvfY1If6qTJXVftU31d7s9qsD9937VbViOfhnIDE+GQpIc6X3aneiQDRkROfqSQRJSGz6mSz27Pp/CFyQGstZCpKxInti/3rKSeCxE5PJyUKjikQuEyFqglJQoHUg06O5CMF9oI5EiRC9Tfz5buoZnIqClact5kqJJaEUjL1rN3xgBSDpwiuS35Zbe8U6GJTP9Z39aY+PB9Zzj9dfV6Zk69V87BrT4s6iE0U+5L2N0YvMQQpugyaUiyC6xvO2YyiZQLvWYXzzXr/Y20umna32Wyr5mAu9/uflTm5/HJaFEB01jBi8MSO0X/Q3DdkDCMGfQNyLljsclrMr4zmUDX3zyNfPwwUfRfyMQ591AUHw9sfdTp11UGRR5+SNfN/sB45iwAj+1Imrhvx0TonwrE1CnEI0f1xFdxQedYBh9WAxHohB2czmU3MCXi1/BB0SiIEJzb6gkjHAWKOSBmoZM34/0TEETQ5yWTcEck7RB2onZgIndUJOcxlCWLCWBKNQI9EIUYkCq4jWqzbSjfdtm60xVX2ZHjZj/TaX7AM7Cgvi9YC1IDny7c0BH9h+44vBOuix4JOvO6K0ranGS1Q0D9CQOCXX/fvzv/mpumvtcMQXbLD+lIesD7oy2Qq/TrikFMB6s00SOaVSbw4jEKZzCbbbZGhFW9Qb29/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Application = _t, createdDateTime = _t, replies.id = _t, replies.createdDateTime = _t, NextReplyTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"Application", type text}, {"createdDateTime", type datetime}, {"replies.id", type text}, {"replies.createdDateTime", type datetime}, {"NextReplyTime", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"id", Order.Ascending}, {"NextReplyTime", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"id"}, {{"allrows", each Table.AddIndexColumn(_,"index",0,1)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"allrows", (x)=> Table.AddColumn(x,"previoustime", each try x{[index]-1}[NextReplyTime] otherwise null)}),
    #"Expanded allrows" = Table.ExpandTableColumn(Custom1, "allrows", {"Application", "createdDateTime", "replies.id", "replies.createdDateTime", "NextReplyTime", "index", "previoustime"}, {"allrows.Application", "allrows.createdDateTime", "allrows.replies.id", "allrows.replies.createdDateTime", "allrows.NextReplyTime", "allrows.index", "allrows.previoustime"})
in
    #"Expanded allrows"

 

 

Method2:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZVLT9tAFIX/yigrkJC4r3nuopQFi1SRqNoFsAjgIquJIzkpEv++11gxngGkvrwdHc/5fM/x9fX1DD0jeGBG8mF2Nlu1u6f6oWrNt/Vhv2vMYvfY1If6qTJXVftU31d7s9qsD9937VbViOfhnIDE+GQpIc6X3aneiQDRkROfqSQRJSGz6mSz27Pp/CFyQGstZCpKxInti/3rKSeCxE5PJyUKjikQuEyFqglJQoHUg06O5CMF9oI5EiRC9Tfz5buoZnIqClact5kqJJaEUjL1rN3xgBSDpwiuS35Zbe8U6GJTP9Z39aY+PB9Zzj9dfV6Zk69V87BrT4s6iE0U+5L2N0YvMQQpugyaUiyC6xvO2YyiZQLvWYXzzXr/Y20umna32Wyr5mAu9/uflTm5/HJaFEB01jBi8MSO0X/Q3DdkDCMGfQNyLljsclrMr4zmUDX3zyNfPwwUfRfyMQ591AUHw9sfdTp11UGRR5+SNfN/sB45iwAj+1Imrhvx0TonwrE1CnEI0f1xFdxQedYBh9WAxHohB2czmU3MCXi1/BB0SiIEJzb6gkjHAWKOSBmoZM34/0TEETQ5yWTcEck7RB2onZgIndUJOcxlCWLCWBKNQI9EIUYkCq4jWqzbSjfdtm60xVX2ZHjZj/TaX7AM7Cgvi9YC1IDny7c0BH9h+44vBOuix4JOvO6K0ranGS1Q0D9CQOCXX/fvzv/mpumvtcMQXbLD+lIesD7oy2Qq/TrikFMB6s00SOaVSbw4jEKZzCbbbZGhFW9Qb29/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Application = _t, createdDateTime = _t, replies.id = _t, replies.createdDateTime = _t, NextReplyTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"Application", type text}, {"createdDateTime", type datetime}, {"replies.id", type text}, {"replies.createdDateTime", type datetime}, {"NextReplyTime", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"id", Order.Ascending}, {"NextReplyTime", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"id"}, {{"allrows", each Table.AddIndexColumn(_,"index",0,1)}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"allrows", (x)=> Table.AddColumn(x,"previoustime",each List.Range(x[NextReplyTime],_[index]-1,1)) }),
    #"Expanded allrows" = Table.ExpandTableColumn(Custom1, "allrows", {"Application", "createdDateTime", "replies.id", "replies.createdDateTime", "NextReplyTime", "index", "previoustime"}, {"allrows.Application", "allrows.createdDateTime", "allrows.replies.id", "allrows.replies.createdDateTime", "allrows.NextReplyTime", "allrows.index", "allrows.previoustime"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded allrows", {"allrows.previoustime", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Extracted Values", {{"allrows.previoustime", ""}})
in
    #"Replaced Errors"

 

Then you can easily calculate the difference.

Reference: Value from previous row – Power Query, M language – Trainings, consultancy, tutorials

You can also download my attachment for more details.

 

Best Regards,

Stephen Tao

 

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