Substract value from the previous row within group using Query M

[Anonymous]

The following is a snapshot of my dataset. I have column A, B, datetime, rank by group from left to right. I grouped the data by A and B, and rank within group by datetime.

I would like to add a new column which shows me the datetime difference with the previous row within each group, say [DiffByGroup]. From there, I will have to

• Mark the [DiffByGroup] if the number exceeds 15, this will be done by adding a new column, [Exceeds15]
• Break the group to two by the [Exceeds15] and assign the the second part of the group a different B name
• GroupBy the dataset again to get max and min datetime for reach group

With all that, my data will be ready for masurements and visuals. I see examples of creating [DiffByGroup] with the previous row using DAX, but then I will have to switch back and forth between Query M and DAX. Is it possible to get the datetime difference column created using Query M? Thank you!

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the following steps to meet your requirement.

1. We need to add a new column that is [rankbygroup]-1.

2. Then we need to merge itself based on two columns.

3. At last we can select Datetime column and Inserted column, and select Time -> Subtraction.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdDBDYAgDIXhVUzPJva1BYVVCPuvIQYPkkj1RsKXv9BSCDvTSokZau0gLLxh0wXI4MzXJaiuPpQOpUN7g3FpOdWh6EAbimkCW24fig48/hZTh9phmEDLGp+jBRMYsuIvHH7twfh8owfv9dgnvNcTqNYT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, datetime = _t, rankbygroup = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"datetime", type datetime}, {"rankbygroup", Int64.Type}}),
    #"Inserted Addition" = Table.AddColumn(#"Changed Type", "Addition", each [rankbygroup] + -1, type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Addition", {"A", "rankbygroup"}, #"Inserted Addition", {"A", "Addition"}, "Inserted Addition", JoinKind.LeftOuter),
    #"Expanded Inserted Addition" = Table.ExpandTableColumn(#"Merged Queries", "Inserted Addition", {"datetime"}, {"Inserted Addition.datetime"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Inserted Addition",{{"A", Order.Ascending}, {"rankbygroup", Order.Ascending}}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Sorted Rows", "Subtraction", each [Inserted Addition.datetime] - [datetime], type duration)
in
    #"Inserted Time Subtraction"

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Anonymous]

Thank you for the detailed explanation and pics!

I had to do +1 instead of -1 for the first step (adding addition column), or I would get the following row, not the previous row. After that, everything worked just the way I wanted. Thank you!

[Greg_Deckler]

@Anonymous - @ImkeF can probably help with the M. The DAX way See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586can be found here.