Forum Discussion

istvan_gorgey's avatar
istvan_gorgey
Frequent Visitor
8 years ago

Insightly with Power BI Desktop

Dear Forum Members,   I'm suffering a bit with Insightly data connection. I've added PowerBI data source using the Insightly connector and now I have the possibility to define measured values. Ot...
istvan_gorgey's avatar
istvan_gorgey
Frequent Visitor
8 years ago

Hi,

 

I actually dropped to use the out-of-the-box Insightly PowerBI Add-On because it is not useful if you would like to do some calculations.

 

I'm not a developer but I created a JSON code what you can use to connect Insightly's API connection (documented here: https://api.insight.ly/v2.2/Help). I used v2.1 API because with v2.2 I should use pagination to sync more than 100 data elements what would hard for me to develop... With v2.1 you do not have any limitation regarding data sync. 

 

Here is my code where you should paste your API KEY with Base64 encoded. This is under Query creation in Advanced Editor.

 

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.1/Opportunities", [Headers=[Authorization="Basic APIKEY", ContentType="application/json"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {
    "OPPORTUNITY_ID",
    "OPPORTUNITY_NAME",
    "OPPORTUNITY_DETAILS",
    "PROBABILITY",
    "BID_CURRENCY",
    "BID_AMOUNT",
    "BID_TYPE",
    "BID_DURATION",
    "OPPORTUNITY_VALUE",
    "FORECAST_CLOSE_DATE",
    "ACTUAL_CLOSE_DATE",
    "CATEGORY_ID",
    "PIPELINE_ID",
    "STAGE_ID",
    "OPPORTUNITY_STATE",
    "OPPORTUNITY_STATE_REASON_ID",
    "IMAGE_URL",
    "RESPONSIBLE_USER_ID",
    "OWNER_USER_ID",
    "DATE_CREATED_UTC",
    "DATE_UPDATED_UTC",
    "VISIBLE_TO",
    "VISIBLE_TEAM_ID",
    "VISIBLE_USER_IDS",
    "CUSTOMFIELDS",
    "TAGS",
    "LINKS",
    "CAN_EDIT",
    "CAN_DELETE"},{
    "OPPORTUNITY_ID",
    "OPPORTUNITY_NAME",
    "OPPORTUNITY_DETAILS",
    "PROBABILITY",
    "BID_CURRENCY",
    "BID_AMOUNT",
    "BID_TYPE",
    "BID_DURATION",
    "OPPORTUNITY_VALUE",
    "FORECAST_CLOSE_DATE",
    "ACTUAL_CLOSE_DATE",
    "CATEGORY_ID",
    "PIPELINE_ID",
    "STAGE_ID",
    "OPPORTUNITY_STATE",
    "OPPORTUNITY_STATE_REASON_ID",
    "IMAGE_URL",
    "RESPONSIBLE_USER_ID",
    "OWNER_USER_ID",
    "DATE_CREATED_UTC",
    "DATE_UPDATED_UTC",
    "VISIBLE_TO",
    "VISIBLE_TEAM_ID",
    "VISIBLE_USER_IDS",
    "CUSTOMFIELDS",
    "TAGS",
    "LINKS",
    "CAN_EDIT",
    "CAN_DELETE"})
in
    #"Expanded {0}"

 

If you need further assistance feel free to contact me.

BR, István

renquism's avatar
renquism
Regular Visitor
8 years ago

Thank you so much! This was exaclty what I was looking for.  One other question, how do you handle the custom fields in Insightly?  The custom fields are bundled together and when I run this query for the custom field name and the custom field value (there can be many values for custom field name), I get a null for the column.