Forum Discussion
Subtracting different columns from different rows
Good afternoon all,
I am trying to find a way to subtract specific date/time fields that are on seperate rows based on a distinct ID(Ticket ID) Here is a sample data with expected outcome. Any help would be greatly appreciated
Time to Submission = Submitted Time - Created Time
Time to Approval = Approved Time - Submitted time
Time to Shipment = Shipped Time - Approved Time
| Ticket ID | Created Time | Submitted Time | Approved Time | Shipped Time | Time to Submission | Time to Approval | Time to Shipment |
| 4724012 | 1/2/2024 17:51:10 | 1/2/2024 18:51:10 | 1:00:00 | 1:39:30 | 38:56:42 | ||
| 4724012 | 1/2/2024 20:30:40 | 1:00:00 | 1:39:30 | 38:56:42 | |||
| 4724012 | 1/4/2024 11:27:22 | 1:00:00 | 1:39:30 | 38:56:42 | |||
| =Submitted Time - Created Time | =Approved Time - Submitted Time | =Shipped Time - Approved Time |
Here is an example of how to do this in Power Query. You can do it in DAX, but it is much more involved.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYyxCcAwDARXEaoNlj4KCr+K0f5rGBJjkibFNcf9j6GRCHNoU+/oMIR48nS6fdy1ndxUe29lsXsYD2PYT/v0sf6dSAJaNQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket ID" = _t, #"Created Time" = _t, #"Submitted Time" = _t, #"Approved Time" = _t, #"Shipped Time" = _t]), #"Changed Type" = Table.TransformColumnTypes( Source, { {"Ticket ID", Int64.Type}, {"Created Time", type datetime}, {"Submitted Time", type datetime}, {"Approved Time", type datetime}, {"Shipped Time", type datetime} } ), #"Grouped Rows" = Table.Group( #"Changed Type", {"Ticket ID"}, { {"_nestedTable", each _, type table [Ticket ID=nullable number, Created Time=nullable datetime, Submitted Time=nullable datetime, Approved Time=nullable datetime, Shipped Time=nullable datetime]} } ), Custom1 = Table.TransformColumns( #"Grouped Rows", { {"_nestedTable", each Table.FirstN(Table.FillUp(_, {"Created Time", "Submitted Time", "Approved Time", "Shipped Time"}), 1)} } ), Custom2 = Table.TransformColumns( Custom1, { {"_nestedTable", each Table.AddColumn(_, "Time to Submission", each [Submitted Time] - [Created Time], type duration)} } ), Custom3 = Table.TransformColumns( Custom2, { {"_nestedTable", each Table.AddColumn(_, "Time to Approval", each [Approved Time] - [Submitted Time], type duration)} } ), Custom4 = Table.TransformColumns( Custom3, { {"_nestedTable", each Table.AddColumn(_, "Time to Shipment", each [Shipped Time] - [Approved Time], type duration)} } ), #"Expanded _nestedTable" = Table.ExpandTableColumn( Custom4, "_nestedTable", {"Created Time", "Submitted Time", "Approved Time", "Shipped Time", "Time to Submission", "Time to Approval", "Time to Shipment"}, {"Created Time", "Submitted Time", "Approved Time", "Shipped Time", "Time to Submission", "Time to Approval", "Time to Shipment"} ), #"Changed Type1" = Table.TransformColumnTypes( #"Expanded _nestedTable", { {"Created Time", type datetime}, {"Submitted Time", type datetime}, {"Approved Time", type datetime}, {"Shipped Time", type datetime}, {"Time to Submission", type duration}, {"Time to Approval", type duration}, {"Time to Shipment", type duration} } ) in #"Changed Type1"
9 Replies
- jgeddesSuper User
Here is an example of how to do this in Power Query. You can do it in DAX, but it is much more involved.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYyxCcAwDARXEaoNlj4KCr+K0f5rGBJjkibFNcf9j6GRCHNoU+/oMIR48nS6fdy1ndxUe29lsXsYD2PYT/v0sf6dSAJaNQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ticket ID" = _t, #"Created Time" = _t, #"Submitted Time" = _t, #"Approved Time" = _t, #"Shipped Time" = _t]), #"Changed Type" = Table.TransformColumnTypes( Source, { {"Ticket ID", Int64.Type}, {"Created Time", type datetime}, {"Submitted Time", type datetime}, {"Approved Time", type datetime}, {"Shipped Time", type datetime} } ), #"Grouped Rows" = Table.Group( #"Changed Type", {"Ticket ID"}, { {"_nestedTable", each _, type table [Ticket ID=nullable number, Created Time=nullable datetime, Submitted Time=nullable datetime, Approved Time=nullable datetime, Shipped Time=nullable datetime]} } ), Custom1 = Table.TransformColumns( #"Grouped Rows", { {"_nestedTable", each Table.FirstN(Table.FillUp(_, {"Created Time", "Submitted Time", "Approved Time", "Shipped Time"}), 1)} } ), Custom2 = Table.TransformColumns( Custom1, { {"_nestedTable", each Table.AddColumn(_, "Time to Submission", each [Submitted Time] - [Created Time], type duration)} } ), Custom3 = Table.TransformColumns( Custom2, { {"_nestedTable", each Table.AddColumn(_, "Time to Approval", each [Approved Time] - [Submitted Time], type duration)} } ), Custom4 = Table.TransformColumns( Custom3, { {"_nestedTable", each Table.AddColumn(_, "Time to Shipment", each [Shipped Time] - [Approved Time], type duration)} } ), #"Expanded _nestedTable" = Table.ExpandTableColumn( Custom4, "_nestedTable", {"Created Time", "Submitted Time", "Approved Time", "Shipped Time", "Time to Submission", "Time to Approval", "Time to Shipment"}, {"Created Time", "Submitted Time", "Approved Time", "Shipped Time", "Time to Submission", "Time to Approval", "Time to Shipment"} ), #"Changed Type1" = Table.TransformColumnTypes( #"Expanded _nestedTable", { {"Created Time", type datetime}, {"Submitted Time", type datetime}, {"Approved Time", type datetime}, {"Shipped Time", type datetime}, {"Time to Submission", type duration}, {"Time to Approval", type duration}, {"Time to Shipment", type duration} } ) in #"Changed Type1" - YcnanPowerBIHelper II
Ok, so realtively new at this but think I may follow, everything before and past this. Can you explain a little?
(Json.Document(Binary.Decompress(Binary.FromText("fYyxCcAwDARXEaoNlj4KCr+K0f5rGBJjkibFNcf9j6GRCHNoU+/oMIR48nS6fdy1ndxUe29lsXsYD2PYT/v0sf6dSAJaNQE=", BinaryEncoding.Base64), Compression.Deflate))If I try this as is, it apples the same info to all rows, so clearly I am doing something incorrectly
- jgeddesSuper User
The entire source step is just a table that I manually entered your sample data. The Binary text is the representation of that sample data.
- YcnanPowerBIHelper II
So would I just overwrite with my table name? My apologies, I am really new to this type of query