Retrieving data from an API in a dataflow

[Charline_74]

Hello

I would like to collect Azure DevOps data via an API in a dataflow.

Do you know how to do it?

Thanks

Cha

[v-tangjie-msft]

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

Copy and transform data from and to a REST endpoint - Azure Data Factory & Azure Synapse | Microsoft...

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. 

[Charline_74]

Hi @v-tangjie-msft,

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-r...) in a dataflow, do you know which connector I should use to retrieve the data via this API

[v-tangjie-msft]

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. 

[Charline_74]

Hi @v-tangjie-msft 

Do you have an example of a script with this API? Because this is the first time I use the M language

[v-tangjie-msft · Accepted solution]

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.