Transforming values from Records into columns and values (from Insightly API - Custom fields)

[Kenji]

Hello all,

I've done a few days of research on how to do this, but I wasn't able to find a proper solution. Thanks everyone in advance!

For background information, I am connecting Power BI desktop to Insightly CRM API (retrieving a custom object) and the values for all the custom fields (CUSTOMFIELDS column of image 1.) come as Lists with Records inside. These records have three values each, one of which is supposed to be the column name, and the other the column value.

Image 1.

Image 2.

Image 3.

What I want to do is to create one column for each Record (Image 2.) where the column name equals the FIELD_NAME and the value equals the FIELD_VALUE of image 3).

PS: There are about 90 Records (image 2.) so a way to do it all at once would be great.

Any support is super appreciated!

[FarhanAhmed · Accepted solution]

When you are expanding the List from image 1, what results are you getting ?

Try to expand List from image 1 and then expand records that comes as a results from expanding list.

Work with a List, Record, or Table structured column (Power Query) (microsoft.com)

[tomlikesputers]

I can't work this out either and that link hasn't shed light (or I'm missing something obvious. Are you able to share a bit more detail on the steps you took to get this working? I want to get multiple custom fields into a single row 😅

[Kenji · Accepted solution]

Thanks for the support! If it wasn't for your help, I wouldn't have been able to find the solution.

After expanding the List and the Records into the table, I used the 'Pivot Column' functionality to do what I wanted!

[FarhanAhmed · Accepted solution]

When you are expanding the List from image 1, what results are you getting ?

Try to expand List from image 1 and then expand records that comes as a results from expanding list.

Work with a List, Record, or Table structured column (Power Query) (microsoft.com)

[Kenji]

When I do that, they both expand, but they end up duplicating the rows and their values (the part in blue of the image below is the same value and should be a single row).

Is there a way to eliminate duplicates and merge the fields? Making the value of the column CUSTOMFIELDS.FIELD_NAME the column's name and the CUSTOMFIELD.FIELD_VALUE the value?