Setting Up Asana API for Power BI – Detailed Instructions Needed

[Kaycee]

Hi everyone,

I’ve recently started using Asana for time tracking but quickly realised that their reporting options are quite limited. While there is a Power BI native connector, it’s only available for enterprise plans, which means I need to set up the API connection from scratch.

I have a link to Asana’s API documentation, which outlines the available fields, but I’m not sure how to configure Power BI to pull this data via API.

Specifically, I’d like to:

• Authenticate and connect to Asana’s API in Power BI
• Extract relevant time tracking data
• Handle pagination (if required)

Has anyone done this before or come across a detailed guide on how to set up the base API connection for Asana in Power BI? If you’ve built something similar, I’d love to see an example M code snippet or step-by-step instructions to get started.

Any help would be greatly appreciated!

Note I'm aware there is a native connector but it only brings in tasks so it is very limited, and only available to Enterprise Customers.

Here’s the link to the API documentation: https://forum.asana.com/t/native-time-tracking-read-data/261390/9

Thanks in advance!

[Anonymous]

Hi @Kaycee ,

This article provides a step-by-step guide on integrating Power BI with Asana, including how to connect, key features, and use cases.

Power BI integration

Best Regards,
Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Kaycee]

Hi @Anonymous, 

Thanks for taking the time to reply. Those instructions are for the Native Power BI integration with Asana.  This however is limited to task information and is only available to Enterprise customers.  To obtain Time Tracking information, Asana themselves recommend setting up an API. 

Kaycee

[OwenAuger · Accepted solution]

Hi @Kaycee 

I saw your query and was intrigued to try to figure this out.

Caveat: I don't specialise in querying APIs with Power Query, but have previously worked on building some queries for the Power BI REST API.

I have attached a PBIT file (in a ZIP file) with some initial suggested queries. Please have a look at the M code to see how it's put together 🙂

To test out these queries:

1. You will need to have an Asana Personal Access Token (PAT) which you can generate from the  Asana developer console.

2. When you open the PBIT, enter the Personal Access Token in the parameters dialog box.

In this PBIT, the PAT is stored directly as a parameter, but, in production, I would suggest using a key vault of some sort to access the PAT.

3. Click Load, then the loaded tables will refresh.

4. The Asana-sourced tables in this test model are:

Table	API endpoint(s) used
Workspaces	https://app.asana.com/api/1.0/workspaces
Projects	https://app.asana.com/api/1.0/projects
Tasks	https://app.asana.com/api/1.0/projects https://app.asana.com/api/1.0/projects/{project_gid}/tasks https://app.asana.com/api/1.0/tasks
Time Tracking	https://app.asana.com/api/1.0/projects https://app.asana.com/api/1.0/projects/{project_gid}/tasks https://app.asana.com/api/1.0/tasks/{task_gid}/time_tracking_entries

Notes on the queries

The queries themselves are built from functions that take the Token and one or more other parameters.

For example, get-Projects is:

let
  output = (Token as text) =>
    let
      structure = [Headers = [accept = "application/json", authorization = "Bearer " & Token]],
      apiCall = Json.Document(Web.Contents("https://app.asana.com/api/1.0/projects", structure)),
      data = apiCall[data],
      resultTable = Table.FromRecords(
        data,
        type table [gid = text, name = text, resource_type = text]
      ),
      renamedColumns = Table.RenameColumns(
        resultTable,
        {
          {"gid", "Project ID"},
          {"name", "Project Name"},
          {"resource_type", "Project Resource Type"}
        }
      )
    in
      renamedColumns
in
  output

The get-TaskTimeTracking function contains an example of pagination. The function List.Generate handles the pagination, in a similar manner to a while loop. It was a little tricky to construct the query in such a way that Power BI didn't see it as a "dynamic data source" which would cause trouble with refresh.

Refresh in Power BI Service

I was able to refresh all the queries in this PBIT in the Power BI Service.

To get refresh working:

• Set all API data sources to "Anonymous" credentials (since the PAT is used to authenticate within the query itself).
• Disabled Gateway for the semantic model.
• Ticked "Skip test connection" when setting the credentials in the Power BI Service, as there seemed to be trouble testing the connection.

The settings should look something like this:

Other Notes:

1. In creating the queries, I followed a similar structure to Štěpán Rešl in his repository with Power Query functions for the Power BI REST API. I learnt quite a bit from here actually!
2. I found the REST API Reference very useful for testing different API endpoints.
3. I have renamed/selected columns in a fairly arbitrary way, so you will no doubt want to change those settings!

Hopefully that's helpful enough to get you started!

Regards,

Owen