Custom fields in the Power BI app

custom-fields
powerbi

#1

Hi all. I just watched the excellent webinar on the API and third-party integrations. Inspired by that, I went back to the Power BI app and started messing around. I discovered it could do a lot more than I’d originally thought. However, the big black hole in it for my team is custom fields. We track costs, time spent, product types and sectors through custom fields, and our monthly reporting is mostly based around them.

Does anyone know of a way to add custom fields to a report in the Power BI app?

Thanks


#2

Hey @Mark_Hudson,

Thanks for reaching out. Microsoft built the Asana BI content pack and unfortunately it does not include custom fields data. I suggest that you reach out to BI support and ask that they add custom fields to the Asana content pack. I will also ask our contacts at BI to see if they can add this feature.

In the meantime, you could import your own data source to BI instead of using the pre-built Asana content pack. Depending on your requirements, this might require some development work on your end.

Here are a few options of what you could do to get BI reports that include custom fields (and any other Asana data you want to include):

  • Export your Asana project to CSV and then upload the CSV to Power BI (no coding required, but a bit manual)
  • Write a script that gets your Asana data via the Asana API, then upload it to BI
  • Use our Asana2SQL Python script to export your data to SQL, then upload to BI

For your reference, here is an article that outlines the data source options for Power BI.

I hope that helps. Let me know if you have any other questions. Also, please share if you end up building anything.

Cheers!
Jeff


#3

Hi @Jeff_Schneider

Thanks so much for getting back to me. At the moment, we’ve gone for option 1 on your recommendations. I set up a master Excel workbook, into which I export some summary data from the Google Sheets dashboard report, combined with data from a more detailed Asana Advanced Search Report. The Power BI report draws from the workbook, which we update each month. It was very time-consuming setting it all up, but we’ve got the process quite streamlined now. Still, it would save us a load of time if I could just have it all pull directly through to the Power BI content pack. I’ll contact BI support as you suggested.

I don’t know how to code, unfortunately, but I have just signed up to Code Academy. Would you say Python would be the most useful language to learn? I’m starting from absolute zero, but I’m hoping that even if I know the basics I’ll be able to get more from the API. That was a great webinar that you did with @Matt_Bramlage, by the way.

Thanks again.
Mark


#4

Hi, any workarounds to get reports on custom fields or labels ?


#5

Hi @Mark_Wright

If you export a project from the drop-down next to the project’s title, the resulting CSV includes custom fields and tags. I have an Advanced Search report based on 42 projects, which is just a very long list in the IN PROJECTS box. Once I’ve got that information in Excel, I can then use that data to create a Power BI report based on all kinds of variables.

Bridge24 gives you all of the custom fields and tags too. However, it doesn’t show tasks that have been completed, which is one of our reporting requirements, so it’s not quite good enough for us. The direct export from Asana is still the best option for our needs.

Hope it gives you what you’re looking for.


#6

Hey @Mark_Hudson, thanks for the kind words about the webinar – happy to hear you found it useful. Good for you for learning to code. I do think Python is a great place to start. We do have a technical services team if you wanted to hire Asana to build the integration.

I’ll let you know if I make any progress on getting BI to add custom fields to the content pack.

Thanks,
Jeff


#7

Hey Mark, if you need custom reporting for your custom fields, I suggest you use Bridge24 for Asana.


#8

Hey @Daniel_Raymond

Yeah, we did try Bridge24 at first but it doesn’t show tasks that have been completed, which is one of our reporting requirements; we need to know how many tasks associated with a particular custom field have been completed in the last quarter. It was almost there but not quite. I checked with the support team there and they said there are no plans to add the feature.

Aside from that, though, I do agree that it’s a great integration.


#9

Simply create a custom filter (using the left panel) and select “completed tasks”. You could see all tasks for a given projects, including completed tasks with all custom fields values.


#10

Thanks! I hadn’t tried using filters yet. I’ll give that a go. :+1:


#11

Just a quick follow-up for anyone else in my position. Bridge24 turned out to be a great potential solution to our problem. However, the free version only allows an export of up to 50 tasks, and our project was bigger than that. Unfortunately, my manager can’t allocate any more budget to this so, for now, I’ll have to stick to exporting directly from Asana. Great application, though.


#12

Our Screenful add-on can report this out-of-the-box. You can give it a try:

  1. Sign up for a free 21 day trial
  2. Go to “Completed Tasks” screen
  3. Select “Quarterly breakdown” from the drop down menu
  4. Select the custom field from the “group-by” section of the same menu

This produces a stacked bar chart showing the number of tasks, having a specific custom field, completed per quarter.

The only caveat is that by default your dashboard contains only 30 days of history. However, you can email at support@screenful.com and request longer history to be loaded.


#13

Thanks very much for the recommendation, @Sami_Linnanvuo. Sadly, I don’t think my team manager will authorise any further expenditure on new software, so I wouldn’t get past the free demo.

It looks very good, though, I have to say.


#14

Velocity is another Asana integration that allows you to create reports based on custom fields using a BI-like interface (disclosure: I’m a cofounder of Velocity). You can use custom fields as metrics (y-axis), dimensions (x-axis, group by), and/or filters to create a custom report:

To try this out, sign up for a free trial, and then click on “Create” in the top nav bar.

@Mark_Hudson It sounds like your team manager won’t authorize further expenditure on new software, but if that changes, feel free to reach out to me if you have any questions about Velocity. Hopefully other folks who are looking for a similar solution may find this helpful.