Pivot column to extract a field

[rachelbi]

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?

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

Any advice is much apprecaited... 

[BA_Pete · Accepted solution]

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