Setting Up Asana API for Power BI – Detailed Instructions Needed

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:

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