Forum Discussion
YcnanPowerBI
2 years agoHelper II
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...
- 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"
jgeddes
2 years agoSuper 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"