Export data to excel from State graph in devops

[Az_Pre4me]

Apologies I am new in a company and using Azure devOps for the first time... 

We follow a sprint lifecycle in for each user story as it passes from developments to test to production. I would like to be able to get dates from azure to track how long this takes...  I want to export the data captured as part of the state graph for all user stories or for a selection of sprints.  I would like a column set something like this ... 

See picture attached 

So criteria columns names might be as follows and the data is in the [...]

  UserID_[number], Uer story_[Title], Sprint iteration_[number_16], new_[date created],  moved to QA[date created], inproduction[datedeployed] etc 

I have excel plugin but this doesn't seem to help.  

Has anyone ever done this before? or have any idea where i might start.

[#2]

@Az_Pre4me did you figure this out?  how or link to solution?

[#3]

Hello @Az_Pre4me , @SagarwalHCSW 

I'm very interested in the subject.

If anyone has the solution, please share it with us.

Thank you very much.

[#4]

@Az_Pre4me  Has anyone found the way to do this?

[#5]

Hi everyone

I can offer another solution without exporting to Excel.

A Time in State for Azure DevOps extension calculates how long a Work Item is in a particular state. The reports are generated automatically; you can choose any period, set up calendars, etc. There are also charts that visualize the data obtained in the report. Here is a link to it:https://marketplace.visualstudio.com/items?itemName=SaaSJet.tis-azure

[#6]

Hello,
If you have a custom State Graph or are referring to a specific representation and want to export its data to Excel, Azure DevOps not offer a direct "export" option. However, you can always create a query that fetches the work items represented in your State Graph and then export the data to Excel.

[#7]

Hello, everyone!
I have an update on the extension: https://marketplace.visualstudio.com/items?itemName=SaaSJet.tis-azure , which I recommended earlier.

Now, an export to Excel is available there, and you can, according to the generated Query, download all Work Items with their States of interest.

[#8]

 did anyone find a way to write a query o PBI query to obtain this without a plugin? 

[#9]

@Michal_Koska 

Found it here https://learn.microsoft.com/en-us/azure/devops/report/powerbi/create-timeinstate-report?view=azure-d...

[#10]

@Az_Pre4me 

For this i can suggest you to try out the Time in State for Azure DevOps extension. In addition to calculating cycle time, time in state or time in Board Columns, transition count, and more. You can customize your work calendars to suit your needs. I think you’ll find it helpful.