Forum Discussion
Retrieving data from an API in a dataflow
Hello
I would like to collect Azure DevOps data via an API in a dataflow.
Do you know how to do it?
Thanks
Cha
Hi Charline_74 ,
In the power query editor, open "Advanced Editor" and enter the following m language.
let Source = Json.Document(Web.Contents("https://dev.azure.com/{organization}/_apis/projects?api-version=7.1")), value = Source[value], #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "description", "url", "state"}, {"id", "name", "description", "url", "state"}) in #"Expanded Column1"Please refer to the following documents for more information.
Get started with the REST APIs for Azure DevOps Services and Azure DevOps Server
REST API samples - Azure DevOps | Microsoft Learn
https://www.youtube.com/watch?v=GZvSYJDk-us
https://www.dataquest.io/blog/python-api-tutorial/
https://www.developer.tech.gov.sg/products/categories/data-and-apis/index.html
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Charline_74 ,
To collect Azure DevOps data via an API in a dataflow, you need to use the REST APIs for Azure DevOps Services and Azure DevOps Server. These APIs allow you to create, retrieve, update, or delete data from your Azure DevOps projects using HTTP methods such as GET, POST, PUT, or PATCH.
You can use the Power Platform dataflows to ingest, transform, and store data from various sources, including REST endpoints. Dataflows let you use the familiar Power Query experience to create and manage your data preparation logic.
You can also use the Azure Data Factory to copy and transform data from and to a REST endpoint using the generic REST connector. This connector supports different HTTP methods and authentication types.
Please refer to the following documents for more information.
Get started with the REST APIs for Azure DevOps Services and Azure DevOps Server
Create and use dataflows in Microsoft Power Platform - Power Query | Microsoft Learn
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous,
Thank you for your answer,
I would like to use this api (https://learn.microsoft.com/en-us/rest/api/azure/devops/wit/work-item-types/list?view=azure-devops-rest-7.1&tabs=HTTP) in a dataflow, do you know which connector I should use to retrieve the data via this API
Hi Charline_74 ,
To use the REST APIs in a dataflow, you can use the **Web** connector in Power Query. This connector allows you to import data from web pages and web services that use the HTTP protocol.
To use the Web connector, you need to specify the URL of the API endpoint and provide any authentication or query parameters that are required. For example, to list the work item types for a project, you can use the following URL:
`https://dev.azure.com/{organization}/{project}/_apis/wit/workitemtypes?api-version=7.1`
You can also use the **Advanced Editor** in Power Query to write custom queries using the M language. This gives you more flexibility and control over how you access and transform the data from the API.
Please refer to the following documents for more information.
Solved: Dataflows - Web API - authentication - Microsoft Power BI Community
Power BI REST API Connector — The Power User
Mapping data flows - Azure Data Factory | Microsoft Learn
Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
