oData to get records from tables by joining another table

[prabhatnath]

Hi Friends,

I am trying to pull records from Azure DevOps using oData and following the reference page https://learn.microsoft.com/en-us/azure/devops/report/powerbi/data-connector-dataset?view=azure-devo... 

I want to pull records from the WorkItemRevisions and I am able to pull the records but it is huge, so I want to pull the records from the WorkItemRevisions by joining the WorkItems as I can easyly query the WorkItems based on Area/AreaPath specific to my projects instead of all projects.

Any clue on how can I get data from oData by relating 2 tables?

Thanks,

Prabhat

[Anonymous]

Hi @prabhatnath 

You can first get all work item IDs into a table column, then call the following OData query for each work item to get their histories. You can create a custom function that calls the OData query, then invoke this custom function to add a custom column to the original table that has the work item column. 

Query work tracking data using Analytics - Azure DevOps | Microsoft Learn

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItemRevisions?
  $filter=WorkItemId eq {Id}
  &$select=WorkItemId, Title, State, Iteration Path

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[amitchandak]

@prabhatnath , You flow is
Projects -> Work Itemx , Work Order Revisions

You fact will be

Work Order Revision Fact Using --- Projects -> Work Items , Work Order Revisions 

You will have dim not joining with wach other

Projects - from Projects

and  Work Item  using Projects,  Work Items

Try that there are no two paths of loop

You can use merge as a new table in Power query to create your tables

Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo

[prabhatnath]

Thanks @amitchandak for your input.

Actually the Org has just one project and multiple sub projects that we can identify using Area/AreaPath. Out of those Sub projects my report need just ~20 of those. I am good at pulling the projects work items from WorkItems table but as there are many Sub projects so it is difficult to hardcode the ArePathSK while pulling the Work Items hostory and the data in this table is huge if I dont use any condition or join that will get me hostory of all subprojects for my merge and load/refresh  time will be high too.

Any suggestions?

Thanks,

Prabhat