Expand Column - REST API JSON Records

[istvan_gorgey]

Hi All,

I have a little trouble with an API call in Insightly. I would like to see organization addresses as well in my reports

I have the following API description: https://api.insight.ly/v2.2/Help#!/Organisations/GetOrganisations

If I do the first expansion I can see a list of my values. That's great but do not know how to map them back.

I tried the following but I'm getting an error.

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Organisations", [Headers=[Authorization="Basic <MYKEY>", ContentType="application/json"]])),
    Organisations = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expand_1 = Table.ExpandRecordColumn(Organisations, "Column1", {  

    "ORGANISATION_ID",
    "ORGANISATION_NAME",
    "BACKGROUND",
    "IMAGE_URL",
    "OWNER_USER_ID",
    "DATE_CREATED_UTC",
    "DATE_UPDATED_UTC",
    "VISIBLE_TO",
    "VISIBLE_TEAM_ID",
    "VISIBLE_USER_IDS",
    "ADDRESSES",
    "CONTACTINFOS",
    "DATES",
    "TAGS",
    "LINKS",
    "ORGANISATIONLINKS",
    "CAN_EDIT",
    "CAN_DELETE",
    "SOCIAL_LINKEDIN",
    "SOCIAL_FACEBOOK",
    "SOCIAL_TWITTER"
 }),
    Addresses= Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expand_2 = Table.ExpandRecordColumn(expand_1, "ADDRESSES", {"ADDRESS_ID"}, {"ADDRESS_ID"})

in
expand_2

Error code: Expression.Error: We cannot convert a value of type List to type Record.
Details:
    Value=List
    Type=Type

Do you have any idea how to map it? It is also good for me to have 2 queries than connect them somehow into relations.

Thanks in advance!

BR, István

[v-chuncz-msft · Accepted solution]

@istvan_gorgey,

Based on the error message, you may try Table.ExpandListColumn first.

[istvan_gorgey]

Thanks. I will give it a try.