Hello @pete.langlois,
Thank You for posting your query onto the Forum.
In order to achieve the results based on the scenario that you’ve provided, it’s more suitable if it’s done via Power Query. Below is the M Code alongwith the screenshot of the final results provided for the reference -
let
Source = Excel.Workbook(File.Contents("F:\ProgFiles\Dell\Downloads\Data.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job ID", Int64.Type}, {"Change Sequence Date", type date}, {"Department", type text}, {"Sequence ID", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Job ID", Order.Ascending}, {"Change Sequence Date", Order.Descending}, {"Sequence ID", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Job ID"}, {{"FullTable", each _, type table [Job ID=nullable number, Change Sequence Date=nullable date, Department=nullable text, Sequence ID=nullable number]}}),
Custom1 =
Table.TransformColumns(
#"Grouped Rows",
{{"FullTable", each Table.AddIndexColumn(_, "Index")}}),
#"Added Custom" =
Table.AddColumn(Custom1, "Days In Sequence", each
let
AllDataTable = [FullTable],
PrevRowValue =
Table.AddColumn(
AllDataTable, "PrevValue",
each try AllDataTable [Change Sequence Date] {[Index] - 1} otherwise [Change Sequence Date] )
in
PrevRowValue),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Days In Sequence"}),
#"Expanded Days In Sequence" = Table.ExpandTableColumn(#"Removed Other Columns", "Days In Sequence", {"Job ID", "Change Sequence Date", "Department", "Sequence ID", "PrevValue"}, {"Job ID", "Change Sequence Date", "Department", "Sequence ID", "PrevValue"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Days In Sequence",{{"Job ID", Int64.Type}, {"Change Sequence Date", type date}, {"Department", type text}, {"Sequence ID", Int64.Type}, {"PrevValue", type date}}),
#"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each Duration.Days([PrevValue] - [Change Sequence Date]), Int64.Type),
#"Removed Other Columns1" = Table.SelectColumns(#"Inserted Date Subtraction",{"Job ID", "Change Sequence Date", "Department", "Sequence ID", "Subtraction"}),
#"Sorted Rows1" = Table.Sort(#"Removed Other Columns1",{{"Job ID", Order.Ascending}, {"Change Sequence Date", Order.Ascending}, {"Sequence ID", Order.Ascending}})
in
#"Sorted Rows1"
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Thanks and Warm Regards,
Harsh
Days Between Sequence - Harsh.pbix (259.8 KB)