Extracting Data from Azure DevOps

[Shawkins566]

Hi all,

I am trying to extract data from DevOps.  I have used a web connection and linked to my query with a url and personal access token.

It has returned a table with the headings:
queryType, queryResultType, asOf, columns and workItems (both columns and workItems containing Lists>Records, then workItems contains id and url, and columns contains referenceName, name and url. 

I have tried a number of ways to extract the information into a simple table but having no luck.  I did add in a custom column at one point which did actually give me the values but instead of the 193 rows there should have been (as that is how many workItems there should be), I ended up with over 42,000 rows.  I think refrenceName/name was causing the workItem to be returned multiple times for each column but I can't quite work it out.

Does anyone have any ideas?

Many thanks

Sarah

[v-bofeng-msft]

Hi @Shawkins566 ,

If you're concerned that the refrenceName/name column may be causing a large number of duplicate rows or expanding a collapsed table, you might consider using deduplication or grouping functions in Power Query to manage the data more effectively.

I think these links will help you a lot:

Keep or remove duplicate rows (Power Query) 

Grouping or summarizing rows 

Best Regards,
Bof