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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rachelbi
Helper I
Helper I

Pivot column to extract a field

Hi there, I have imported a table from Asana to power bi and there is some list column, I used to extract the "Expand to New Rows" -> Select "name" and "value" -> Unselect "null" -> and I want to the the "Estimated time" into a separate column, how can I do so?

 

5.PNG

and I tried to "Pivot column" with "Don't aggregate" , error is shown, i.e.

6.PNG7.PNG

 

Any advice is much apprecaited... 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @rachelbi ,

 

It doesn't look as though the error you're getting applies to the pivot step - it looks like it's from a previous one.

Either way, you could try performing these steps to see if it works:

 

Starting from your [Custom Fields] column with nested records, filter out null values.

Expand the nested records, including the [id] and [project_id] columns - this is so that you should have unique row identifiers for the pivot.

Now pivot the [Custom Fields.name] column using [Custom Fields.value] as the value, and choosing "Don't Aggregate" under Advanced Options.

You should now have a row per [id]/[project_id] combination, and [Custom Fields.name] values as columns.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

1 REPLY 1
BA_Pete
Super User
Super User

Hi @rachelbi ,

 

It doesn't look as though the error you're getting applies to the pivot step - it looks like it's from a previous one.

Either way, you could try performing these steps to see if it works:

 

Starting from your [Custom Fields] column with nested records, filter out null values.

Expand the nested records, including the [id] and [project_id] columns - this is so that you should have unique row identifiers for the pivot.

Now pivot the [Custom Fields.name] column using [Custom Fields.value] as the value, and choosing "Don't Aggregate" under Advanced Options.

You should now have a row per [id]/[project_id] combination, and [Custom Fields.name] values as columns.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors