I have a portfolio of projects and I need to create a dashboard using custom field data from the individual projects (I have about 30 customs fields). I have pulled the data into Power BI and expanded the customs field information. I then go to pivot column to get the custom fields into rows. This is where the issue occurs. I have a flattening problem where by each data element is on a different row so for 4 tasks rather than 4 rows I’m getting 30+ rows which means I cant build the dashboards I need. Any ideas how to sort? Have tried Asana support who say its not their problem as it should appear as I want it too.
I am having the same issue. If you have multiple custom fields, when you expand the data and subsequently pivot it, it creates duplicate records instead of merging them. It essentially makes using multiple custom fields to identify/describe a task useless for reporting.
What you could do is create 4 duplicate queries and filter each one by a respective custom field (filter out nulls) and then merge all 4 of them as a new query. Once you get the new query, you can then expand the columns related to the other custom fields and it should give you what you’re looking. You just have to be sure to select the correct join when merging and keep in mind when you filter out the data you will likely filter out other important data, so you’d need to work out of that query solely for reporting on that data.
We finally managed to solve this by a simple grouping of rows based on task ID! also the issue we now see if that the data from Asana doesnt seem to have any way of distinguishing between completed projects and active projects
Great! I’ll give that a shot, too, maybe it’s better than what I’m doing. While I haven’t seen anything that denotes the completed project status, there is a “Project Status Archived” column or something similar that denotes if it’s archived or not, so maybe you could archive completed projects and filter them out with that column.
Do it before pivoting the columns.
Looked into that but couldnt see any difference in how Asana populated this column in power bi
Thanks for the tip.
You might have to wait or create a new connection to see results after archiving. There is a limitation where exported Asana data only updates on first connection and every 12 hours afterwards, stated on the Power BI Integration Asana page.