Forum Discussion
Substract value from the previous row within group using Query M
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!
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.
3 Replies
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 - 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.
