Load Asana custom fields via Power BI API

Integrations
#1

Hej all,
we have a requirement to show some asana-data in our powerbi.
I tried the default asana-api in Power BI and it works except i dont get the custom fields from our asana.
So i was searching for a solution for that named problem and found this forum.
Most of the post are older then one year and i found no “easy” way to get those custom fields outside of asana with power bi. I read with some third party application i can realise named problem.
But, on the asana-page about Power BI itself i read that it is no problem to get access to the Power BI Custom-Fields, so what am i missing here?
Quote(Power BI • Asana): " Visualize your team’s work
Build custom, actionable dashboards based on Asana inputs like Custom Fields to get real-time insights into projects and workflows."

So is there at the moment an “easy” solution for loading custom fields into Power BI? or is coding/a third party necessary?

Thanks for the help!
Greetings
Mischa

1 Like
#2

Hi Mischa,

First, I saw it’s your first time on the forums, welcome!

I just did this in Power BI, when I’m back in front of my work computer I’ll let you know! I’ll toss the steps I took into this thread :smile:

But I do remember it was a lot of intermediary steps…

Cheers!

#3

I’m looking for the same thing!

Currently in Power Query Editor but the custom fields I have created are not expanding with the values I expected.

#4

Hej Will!
can you please give us an update? : D

I am one step further but still not at the finish line.
I get the data now, but in a total messed up way.
It looks like, that i get for every Custom field, every value from every custome field of the task.
That means what was once one dataset become five, because i have my five custome fields.
Looks like that:
image
Just the green marked one is the correct value for the field “Responsible”, every other value comes from another custome field i have.

Thanks for the help.

Greetings
Mischa

#5

I think I’m having the same issue on this - I’m unable to load custom fields from Asana into PowerBI through the direct link. I can load them with a CSV

#6

I’m also with the same problem. We use a few custom fields to control our projects and I need to create a dashboard on Power B.I. Really looking foward for this solution.

#7

I’m experiencing the same. I don’t see custom fields when connecting to a portfolio URL

#8

Hi all @Igor_Siqueira_Neves @Christopher @Mischa_Kiefer @Anthony_Mann

Sorry I forgot about this thread!!

I’m going to get a bit technical but I’m not a super duper power BI developer, I’m a poke around until it works developer. :slight_smile:

  1. Under Transform data, you will want to add asana as a data source with a project or portfolio URL
  2. Save that table, I really don’t do any manipulation, that’s a list of all tasks, by task ID.
  3. Copy and paste that query.
  4. Click the branching arrows at the top of the custom fields column, select Expand to new Rows
  5. Values showing List now say “Record”
  6. Click the branching arrows at the top of the custom fields column again, this time you get a small window of what to select, I leave all checked. and press OK.
  7. I deleted Custom Fields.id, Custom Fields.project_id and Custom Fields.Type, those aren’t relevant for me.
  8. I’m left with 2 new columns, custom fields.name and custom fields.value.
  9. I delete everything except task ID, custom fields.name and custom fields.value.
  10. Some of my fields showed up at “Field Name - Project” so I split column by delimiter of ‘-’ and deleted Custom Field name.2
  11. I filtered custom fields.name.1 to exclude nulls.
  12. Select Custom fields.name.1, and select pivot column under transform ribbon. Select Custom Fields.value, then under Advanced options select don’t aggregate.

End results is a list of Task ID, and a column with each custom field and its value. I then just make a relationship between task ID of the custom field table and the tasks table.

5 Likes
Trying to connect asana overview to powerbi
#9

@Will.S
Very helpful information, I was able to see all my custom fields, assuming this would work for Tags as well. Super, super helpful!!! Thank you!

#10

When i try this method, i get to step 5 I get a datasource error.

#11

When Trying this I get:

DataSource.Error: Web.Contents failed to get contents from ‘https://reporting-api.integrations.asana.plus/v1/data?url=https%3A%2F%2Fapp.asana.com%2F0%2Fportfolio%2F1200055502816624%2Flist’ (404): Not Found
Details:
DataSourceKind=Asana
DataSourcePath=https://app.asana.com/0/portfolio/1200055502816624/list
Url=https://reporting-api.integrations.asana.plus/v1/data?url=https%3A%2F%2Fapp.asana.com%2F0%2Fportfolio%2F1200055502816624%2Flist

#12

at which step?

#13

I stopped getting the previous error, but now I get this at step 12:

Expression.Error: The index is outside the bounds of the record.
Details:
Record=[Record]
Index=2

#14

I was able to solve this.

After step 11, before step 12 → click “add column” on the ribbon, then clicking on “index column”.

#15

Hello, Chris_Batcheller

Thanks for solving the question. After reaching out I will get back to you . Hoping for the best answer for the next topics.

Regards,
Srija,
PowerBI Certification Developer.

#16

Even after I add the index, step 12 gives me the following error:

Expression.Error: We cannot apply operator < to types List and List.
Details:
    Operator=<
    Left=[List]
    Right=[List]
#17