Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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!
Solved! Go to 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.
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.
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!
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |