Calculated column - crate ID number

Hi Anonymous,

I would do this in Power Query. Using your starting data in an Excel workbook, here is my code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\rlosurdo\Desktop\SprintTime.xlsx"), null, true),
    Original_Sheet = Source{[Item="Original",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Original_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type datetime}, {"Seconds", Int64.Type}, {"Velocity", type number}, {"Acceleration", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    Velocities = Table.FromColumns(
	Table.ToColumns(#"Added Index") & 
	Table.ToColumns(
		Table.InsertRows(Table.SelectColumns(#"Added Index","Velocity"),0,{[Velocity = null]})) &
	Table.ToColumns(
		Table.RemoveFirstN(Table.SelectColumns(#"Added Index","Velocity"),1)),
	Table.ColumnNames(#"Added Index") & {"PreviousVelocity", "NextVelocity"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(Velocities,1),
    #"Added SprintAudit" = Table.AddColumn(#"Removed Bottom Rows", "SprintAudit", each if [Velocity] >= 15 then if [PreviousVelocity] < 15 then "Initial" else if [NextVelocity] < 15 or [NextVelocity] is null then "Completion" else "Continued" else null),
    #"Added Join Key 1" = Table.AddColumn(#"Added SprintAudit", "Join Key", each if [SprintAudit] <> null then 1 else 0),
    InitialIndexTable = Table.SelectRows(#"Added Join Key 1", each ([SprintAudit] = "Initial")),
    #"Added Initial Index" = Table.AddIndexColumn(InitialIndexTable, "Sprint Index", 1, 1),
    CompletionIndexTable = Table.SelectRows(#"Added Join Key 1", each ([SprintAudit] = "Completion")),
    #"Added Completion Index" = Table.AddIndexColumn(CompletionIndexTable, "Sprint Index", 1, 1),
    IndexTable = Table.Combine({#"Added Completion Index", #"Added Initial Index"}),
    #"Removed Other Columns" = Table.SelectColumns(IndexTable,{"SprintAudit", "Sprint Index", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[SprintAudit]), "SprintAudit", "Index", List.Sum),
    #"Added Join Key" = Table.AddColumn(#"Pivoted Column", "Join Key", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Join Key 1",{"Join Key"},#"Added Join Key",{"Join Key"},"IndexTable",JoinKind.LeftOuter),
    #"Expanded IndexTable" = Table.ExpandTableColumn(#"Merged Queries", "IndexTable", {"Sprint Index", "Completion", "Initial"}, {"Sprint Index", "Completion", "Initial"}),
    #"Added Sprint ID" = Table.AddColumn(#"Expanded IndexTable", "Sprint ID", each try if [Index] >= [Initial] and [Index] <= [Completion] then [Sprint Index] else 0 otherwise null),
    #"Filtered Rows" = Table.SelectRows(#"Added Sprint ID", each ([Sprint ID] <> 0)),
    Partition = Table.Group(#"Filtered Rows", {"Sprint ID"}, {{"Partition", each Table.AddIndexColumn(_, "Sprint Time Setup",0,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "Sprint Time Setup"}, {"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "Sprint Time Setup"}),
    #"Added Sprint Time" = Table.AddColumn(#"Expanded Partition", "Sprint Time", each if [Sprint ID] <> null then [Sprint Time Setup] else null),
    #"Sorted Rows" = Table.Sort(#"Added Sprint Time",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Sprint Time Setup"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Time", "Seconds", "Velocity", "Acceleration", "Index", "Sprint Time", "Sprint ID"})
in
    #"Reordered Columns"

Here is the resulting table in Power BI:

I basically created a few temporary tables so that I could define and index each sprint as its own entity. Then I used ImkeF's partition magic (https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864) to add an index for each individual Sprint ID.

Let me know if you have any questions or would like me to send over my workbook.