Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am importing data from Asana into PowerBI with the hopes of tracking the amount of time spent on tasks and tracking which users are spending X amount of their time on which tasks. Since this data is being imported directly from Asana, I unfortunately cannot reformat the data at the source. Custom fields can be created in Asana to create the data we want, but all Custom Fields end up being aggregated into a single column in PowerBI, which is not helpful.
One of the things I am trying to do is create a pie chart based on the "Effort" of each task that is applied to an "Operation". In the column named "Custom Fields.name", I would like those 2 fields to be their own columns (named "Effort - Sprint Planning" and "Operations - Sprint Planning", respectively). I would like the data from the column "Custom Fields.value" to be the values that populate these 2 columns, with the decimal values under the "Effort - Sprint Planning" column and names like "Administrative" under the "Operations - Sprint Planning" column. I tried pivoting the columns, but it doesn't keep the Values paired with the appropriate Operation (values and Operations are in separate columns, but the row of data next to say, Values shows as "null", with what should be the paired Operations value in a separate line (and paired with its own "null" value).
How can I re-sort this information into the different columns while keeping the remainder of the data lined up correctly?
Solved! Go to Solution.
Hi @JohnGray ,
thanks for the data. What you have to do is a pivot-operation on your "Custom Fields.name"-column with the parameters like shown below:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @JohnGray ,
thanks for the data. What you have to do is a pivot-operation on your "Custom Fields.name"-column with the parameters like shown below:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @JohnGray ,
that looks doable. Please provide sample data like described here and I can show you the solution: How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello Imke,
Thank you! I have saved the test file here:
https://drive.google.com/file/d/1qYuByO160eqtr-BIRvuAxd9LO9tM0b3s/view?usp=sharing
Hi @JohnGray ,
having problems understanding your request. Could you please paste a screenshot or better even sample data of the desired result?
Please make sure to include all relevant columns.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imke,
So the data currently looks like the screenshot shown above, when I want it to look like tis instead.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |