Forum Discussion

Helper II

2 years ago

Solved

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 h...

Need Help

jgeddes

2 years ago

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"

YcnanPowerBI

Helper II

2 years ago

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

Super User

2 years ago

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
Helper II
2 years ago
I can't thank you enough! You were beyond helpful! I truly appreciate the time and effort you put into helping me.

Forum Discussion

Subtracting different columns from different rows

Related content

Different schema into different destination

Subtracting from two difference tables

Page Level filter for different groups

Difference between different Staging lakehouses

Subtraction calulation