Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Based on the error message, you may try Table.ExpandListColumn first.
Based on the error message, you may try Table.ExpandListColumn first.
Thanks. I will give it a try.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |