Subtracting different columns from different rows

[YcnanPowerBI]

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

[YcnanPowerBI]

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

[jgeddes]

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.

[YcnanPowerBI]

So would I just overwrite with my table name?  My apologies, I am really new to this type of query

[jgeddes]

No problem at all. If you have not done any work in the Advanced Editor it can be tricky at first. 
To start, does your query look like the following picture?

If it does then we can add the majority of my code to the end of your code. I will just need to know the last step in your query in order be able to amend my code correctly.

[YcnanPowerBI]

Source = Excel.Workbook(File.Contents("C:\Users\User\OneDrive - Company\Desktop\RMA Report\RMA_Audit_Detail.xlsx"), null, true),
#"RMA Audit Detail_Sheet" = Source{[Item="RMA Audit Detail",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"RMA Audit Detail_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ticket ID", Int64.Type}, {"Update ticket group", type text}, {"Update ticket assignee", type text}, {"Changes - Field name", type text}, {"Changes - Previous value", type text}, {"Changes - New value", type text}, {"Update - Timestamp", type datetime}, {"Updater name", type text}, {"Ticket created - Timestamp", type datetime}, {"Ticket organization name", type text}, {"Updates", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Update - Timestamp", Order.Ascending}}),
#"Added Custom3" = Table.AddColumn(#"Sorted Rows", "Created Time", each if [#"Changes - Previous value"] = "" and [#"Changes - New value"] = "adv_rma_stage_submitted" then [#"Ticket created - Timestamp"] else ""),
#"Added Custom" = Table.AddColumn(#"Added Custom3", "Submitted Time", each if [#"Changes - New value"] = "adv_rma_stage_submitted" then [#"Update - Timestamp"] else ""),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Approved Time", each if [#"Changes - New value"] = "adv_rma_stage_approved" then [#"Update - Timestamp"] else ""),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Shipped Time", each if [#"Changes - New value"] = "adv_rma_stage_shipped_to_customer" then [#"Update - Timestamp"] else ""),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Created Time", type datetime}, {"Submitted Time", type datetime}, {"Approved Time", type datetime}, {"Shipped Time", type datetime}})
in
#"Changed Type1"

[jgeddes]

This should do it. Replace all of your code with this...

let
    Source = Excel.Workbook(File.Contents("C:\Users\User\OneDrive - Company\Desktop\RMA Report\RMA_Audit_Detail.xlsx"), null, true),
    #"RMA Audit Detail_Sheet" = Source{[Item="RMA Audit Detail",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"RMA Audit Detail_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ticket ID", Int64.Type}, {"Update ticket group", type text}, {"Update ticket assignee", type text}, {"Changes - Field name", type text}, {"Changes - Previous value", type text}, {"Changes - New value", type text}, {"Update - Timestamp", type datetime}, {"Updater name", type text}, {"Ticket created - Timestamp", type datetime}, {"Ticket organization name", type text}, {"Updates", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Update - Timestamp", Order.Ascending}}),
    #"Added Custom3" = Table.AddColumn(#"Sorted Rows", "Created Time", each if [#"Changes - Previous value"] = "" and [#"Changes - New value"] = "adv_rma_stage_submitted" then [#"Ticket created - Timestamp"] else ""),
    #"Added Custom" = Table.AddColumn(#"Added Custom3", "Submitted Time", each if [#"Changes - New value"] = "adv_rma_stage_submitted" then [#"Update - Timestamp"] else ""),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Approved Time", each if [#"Changes - New value"] = "adv_rma_stage_approved" then [#"Update - Timestamp"] else ""),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Shipped Time", each if [#"Changes - New value"] = "adv_rma_stage_shipped_to_customer" then [#"Update - Timestamp"] else ""),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Created Time", type datetime}, {"Submitted Time", type datetime}, {"Approved Time", type datetime}, {"Shipped Time", type datetime}}),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type1", 
        {"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]}
        }
    ),
    Custom4 = 
    Table.TransformColumns(
        #"Grouped Rows", 
        {
            {"_nestedTable", each Table.FirstN(Table.FillUp(_, {"Created Time", "Submitted Time", "Approved Time", "Shipped Time"}), 1)}
        }
    ),
    Custom5 = 
    Table.TransformColumns(
        Custom4, 
        {
            {"_nestedTable", each Table.AddColumn(_, "Time to Submission", each [Submitted Time] - [Created Time], type duration)}
        }
    ),
    Custom6 = 
    Table.TransformColumns(
        Custom5, 
        {
            {"_nestedTable", each Table.AddColumn(_, "Time to Approval", each [Approved Time] - [Submitted Time], type duration)}
        }
    ),
    Custom7 = 
    Table.TransformColumns(
        Custom6, 
        {
            {"_nestedTable", each Table.AddColumn(_, "Time to Shipment", each [Shipped Time] - [Approved Time], type duration)}
        }
    ),
    #"Expanded _nestedTable" = 
    Table.ExpandTableColumn(
        Custom7, 
        "_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 Type2" = 
    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 Type2"

[YcnanPowerBI]

I can't thank you enough!  You were beyond helpful!  I truly appreciate the time and effort you put into helping me.  

[YcnanPowerBI]

You are the best!  Yes, here is a snip

[jgeddes · Accepted solution]

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"