How to write a PowerBI custom function for retrieving workitem status from AzureDevOps?

[Basanth]

Hi,

I am trying to write a custom function for retrieing the status of workitem using AzureDevOps REST api query in PowerQuery.

This is the query which currently I am using and not working,

// Define a function to retrieve work item status from Azure DevOps
GetADOWorkItemStatus = (Organization as text, Project as text, WorkItemID as number, Token as text) =>
    let
        // Build the REST API URL
        url = "https://dev.azure.com/" & Organization & "/" & Project & "/_apis/wit/workitems/" & Number.ToText(WorkItemID) & "?api-version=7.1",
        
        // Set up the request headers with the Personal Access Token
        headers = [#"Authorization" = "Basic " & Text.ToBinary(Token)],
        
        // Make the API request
        response = Json.Document(Web.Contents(url, [Headers=headers])),
        
        // Extract the status from the response
        status = response[fields][State]
    in
        status

With this I am getting an error " Expression.Error: The 'Authorization' header is only supported when connecting anonymously."

Any hint how can I connect to ADO by passing PAT in PoweRQuery?

[Basanth]

Hi,

I could connect to ADO using PAT with the help of Python query.

https://community.fabric.microsoft.com/t5/Community-Blog/Using-regular-expressions-in-power-bi-deskt...

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts

def get_workitem_status(organization, project, work_item_id, personal_access_token):
    authorization = str(base64.b64encode(bytes(':'+ personal_access_token, 'ascii')), 'ascii')
    headers = {
        'Accept': 'application/json',
        'Authorization': 'Basic '+authorization
    }
    try:
        # Make a GET request to the Azure DevOps REST API
         # Azure DevOps REST API base URL
        base_url = f"https://dev.azure.com/{organization}/{project}/_apis/wit"

        # Work item API endpoint
        api_endpoint = f"{base_url}/workitems/{work_item_id}?api-version=7.1"
        response = requests.get(
        url=api_endpoint, headers=headers)

        # Check if the request was successful (status code 200)
        if response.status_code == 200:
            # Parse the JSON response
            work_item_data = response.json()

            # Extract and return the status of the work item
            return work_item_data['fields']['System.State']
        else:
            # Print an error message if the request was not successful
            print(f"Error: {response.status_code} - {response.text}")
            return None

    except Exception as e:
        print(f"Error: {e}")
        return None

For creating a custom column we can use below snippet for creating new column "WorkItemStatus",

organization = "AAA"
project = "BBB"
personal_access_token = "<PAT>"

status_list=[]
for i in range(len(dataset)):
    numberValue=str(dataset.iat[i,1]) //Here 1 is the column number where you have the data
    status = get_workitem_status(organization, project, numberValue, personal_access_token)
    status_list.append(status)
dataset['WorkItemStatus']=status_list

Connection to ADO using PAT using PowerBI Query is still failing.