Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JohnGray
Helper I
Helper I

Sorting columns out based on data

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 keepingPowerBI Separate Values.png the remainder of the data lined up correctly? 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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:

ImkeF_0-1665601736575.png

 

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

View solution in original post

6 REPLIES 6
v-henryk-mstf
Community Support
Community Support

Hi @JohnGray ,

 

Whether the advice given by @ImkeF  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


Looking forward to your feedback.


Best Regards,
Henry

ImkeF
Super User
Super User

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:

ImkeF_0-1665601736575.png

 

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

ImkeF
Super User
Super User

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

ImkeF
Super User
Super User

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. 

 

PowerBI Separate Values2.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.