Show Visual Studio Online Work Item Cycle Times

Question

Ideally, I would like to calculate the cycle time of the whole value stream represented by Product Backlog Items in Visual Studio Online via Power BI. (I would then love to get time per status, i.e. how long was it sat in the "New" state, or how long was it stuck in the "Committed" state.)

To start with, I'm interested in using the calculated value that represents the time between the created date and the closed date of the product backlog item. After this, I'm keen on getting the distribution of values found.

product backlog item time between created timestamp to closed timestamp

Would be a starting point, but this shows "queued duration minutes by title", of course.

Most other attempts result in:

Can’t determine relationships between the fields

Is there a way to get some indication of cycle times?

Answer 1

Assuming each rows has a created date and closed date, you could use the power query duration function to get the difference between the two dates.

https://msdn.microsoft.com/en-us/library/mt296613.aspx

Update: After looking into it (see my comments below) I realize that it takes some Olympic-level power query skills to build the query you need. So I created it for you :). The below works in Power BI desktop, but doesn't refresh in PowerBI.com for some reason (I'm checking into that). To make this work, you need to put your VSO account and the Query ID in the ConnectionInfo record.

Recommend setting up an alternate credential rather than using BASIC auth.

You can get a QueryID by saving a shared query to your VSO and then using curl to retrieve the id using the query:

//Replace YOURACCOUNT and YOURPROJECT with correct values below
curl -u username:password https://YOURACCOUNT.visualstudio.com/DefaultCollection/YOURPROJECT/_apis/wit/queries?$depth=1&api-version=1.0

Then you can use the following query (create a blank query and paste it into the advanced editor in Power BI Desktop). You might need to adjust some of the steps starting at "workitems =" since your query might have different fields than mine did. If you return created, resolved and closed dates, the duration calculations I used should work for you. You'll set the authentication to "Basic" when challenged in Power BI Desktop and supply the required credentials.

let
//TODO: replace YOURACCOUNT and YOURQUERYID below 
ConnectionInfo = [account = "YOURACCOUNT.visualstudio.com", queryID="YOURQUERYID"],
account = Record.FieldValues(ConnectionInfo){0},
rootQuery = let
    queryID = Record.FieldValues(ConnectionInfo){1},
    query = "https://" & account & "/DefaultCollection/PowerBIClients/_apis/wit/wiql/" & queryID,
    Source = Json.Document(Web.Contents(query))
in
    Source,

#"INT-columns" = let
    Source = rootQuery,
    columns = Source[columns],
    #"Converted to Table" = Table.FromList(columns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"referenceName", "name", "url"}, {"referenceName", "name", "url"})
in
    #"Expanded Column1",

#"RAW-workitems" = let
    Source = rootQuery,
    workItems = Source[workItems],
    #"Converted to Table" = Table.FromList(workItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "url"}, {"id", "url"})
in
    #"Expanded Column1",

#"INT-columnClause" = let
    Source = #"INT-columns",
    colNames = Table.RemoveColumns(Source,{"name", "url"}),
    list = Table.ToList(colNames),
    joined = Text.Combine(list, ",")
in
    joined,

#"INT-workitemsToGet" = let
    Source = #"RAW-workitems",
    col = Table.RemoveColumns(Source,{"url"}),
    #"Changed Type" = Table.TransformColumnTypes(col,{{"id", type text}}),
    list = Table.ToList(#"Changed Type"),
    l = List.Count(list),
    limits = List.Generate(()=>0, each _ < l, each _ + 100),
    #"Converted to Table" = Table.FromList(limits, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "iterations"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "itemsToGet", each List.Range(list, [iterations], 100)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "itemsToGetString", each Text.Combine([itemsToGet], ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"iterations", "itemsToGet"})
in
    #"Removed Columns",

#"INT-workitemRequests" = let
    Source = #"INT-workitemsToGet",

    #"Added Custom" = Table.AddColumn(Source, "requests", each "https://" & account & "/DefaultCollection/_apis/wit/workitems?ids=" & [itemsToGetString] & "&fields=" & #"INT-columnClause"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"itemsToGetString"})
in
    #"Removed Columns",

workitems = let
    #"INT-workitemsRequests (2)" = let
    Source = #"INT-workitemRequests",
    results = Table.AddColumn(Source, "Results", each Json.Document(Web.Contents([requests]))),
    out = 1
in
    results,
    #"Removed Columns" = Table.RemoveColumns(#"INT-workitemsRequests (2)",{"requests"}),
    #"Expanded Results" = Table.ExpandRecordColumn(#"Removed Columns", "Results", {"count", "value"}, {"Results.count", "Results.value"}),
    #"Expanded Results.value" = Table.ExpandListColumn(#"Expanded Results", "Results.value"),
    #"Expanded Results.value1" = Table.ExpandRecordColumn(#"Expanded Results.value", "Results.value", {"id", "fields", "url"}, {"Results.value.id", "Results.value.fields", "Results.value.url"}),
    #"Expanded Results.value.fields" = Table.ExpandRecordColumn(#"Expanded Results.value1", "Results.value.fields", {"System.Id", "System.WorkItemType", "System.State", "System.AssignedTo", "System.CreatedDate", "System.Title", "Microsoft.VSTS.Common.ResolvedDate", "Microsoft.VSTS.Common.ClosedDate"}, {"System.Id", "System.WorkItemType", "System.State", "System.AssignedTo", "System.CreatedDate", "System.Title", "Microsoft.VSTS.Common.ResolvedDate", "Microsoft.VSTS.Common.ClosedDate"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Results.value.fields",{"Results.count", "Results.value.id"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Results.value.url", "WorkItemUrl"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Microsoft.VSTS.Common.ResolvedDate", type datetime}, {"Microsoft.VSTS.Common.ClosedDate", type datetime}, {"System.CreatedDate", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DurationToResolved", each Duration.TotalDays([Microsoft.VSTS.Common.ResolvedDate] - [System.CreatedDate])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DurationToClosed", each Duration.TotalDays([Microsoft.VSTS.Common.ClosedDate] - [System.CreatedDate])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"DurationToResolved", type number}, {"DurationToClosed", type number}})
in
    #"Changed Type1"    

in workitems