Connect Power BI Desktop to insightly

[webportal]

Hello,

It doesn't seem possible to connect Power BI Desktop to Insightly although there's a content pack for Power BI Service.

Is there a workaround, if we need to build a data model and/or add calculated measures? E.g. use the content pack for the service and then connect Desktop to Power BI service?

Or any better suggestion? Thank you for helping with this!

[v-yuezhe-msft]

@webportal,

There is no built-in connector for Insightly in Power BI Desktop. You can connect to the Insightly dataset in the Power BI Service from Power BI Desktop following the instructions in this article, but in this case, only Report view is available and we are only able to create measures.

Another workaround is to use “Analyze in Excel” feature in Power BI Service to get Insightly dataset in PivotTable form, then you can connect to the Excel file in Power BI Desktop. For more details, please review Jeremy’s reply in this similar thread.

Regards,
Lydia

[webportal]

Ok, that was also my understanding.

But any chance of creating a custom connector?

Thanks a lot for your help!

[v-yuezhe-msft]

@webportal,

Yes, you can. Another option is to utilize Insightly API to get data and import the data to Power BI Desktop via Get Data->Web entry.

You can review the following articles about Insightly API.
https://api.insight.ly/v2.1/Help
https://support.insight.ly/hc/en-us/articles/204061444-Working-with-the-Insightly-Web-API


Regards,
Lydia

[webportal]

@v-yuezhe-msft thanks a lot for your support!

The API solutions seems perfect, since we need to create a data model with related fields and keep data in synch.

I tried several different calls, but none seems to work. This is the error message I get:

"It's only possible to specify an API Key when an API Key name is supplied"

Any Idea what I'm doing wrong?

[v-yuezhe-msft]

@webportal,

You can specify an API key name in the hearers area of Web.Contents using Advanced Editor. Please review the example in this blog: https://msdn.microsoft.com/en-us/library/mt260892.aspx .

There is also a similar thread for your reference.
https://community.powerbi.com/t5/Desktop/Get-data-from-web-error-A-web-API-key-can-only-be-specified...

Regards,

[webportal]

@v-yuezhe-msft my level of M is 0.

I managed to get to the advanced editor, and from what I see there, I should build a query for each Insightly table (resource) I need to download, and tried this:

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.1/contacts", [Headers=[Accept="application/json", Authorization="api-key xxxxxxxxxxxxxxxxx"]])),
    messages = Source[messages]
in 
     Source

Which, obviously, returns an error. 

Any help would be appreciated!

[v-yuezhe-msft]

@webportal,

What error message do you get? How about you remove "messages = Source[messages]" from the above code?

Regards,
Lydia

[webportal]

Hello, I've got the error: Can't connect with the supplied credentials:

I get the same error, even when deleting "messages = Source[messages]" from the code.

[v-yuezhe-msft]

@webportal,

Please check if the following code works.

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.1/contacts", Headers = [#"Content-Type"="application/json", #"Authorization"="Basic <your_access_token>"]]))
in 
     Source

Regards,
Lydia

[webportal]

@v-yuezhe-msft thanks for helping me with this one!

First, I'm unsure how to get to the advanced editor. I open a new PBIX file and the advanced editor isn't there.... I must trick it by creating a connection to another web page and only then I can reach the advanced editor. But is there a better way to do this?

Second, the code you gave me returns the error: "Comma token was expected" on the last square bracket, which looks like a syntax error.

Third, I removed the last square bracket which seems to have solved the syntax error, but now I get the error: "Expression.Error: O nome "Headers" was not recognised. Make sure it is written correctly.". This is how the code looks like now:

= Json.Document(Web.Contents("https://api.insight.ly/v2.1/contacts", Headers = [#"Content-Type"="application/json", #"Authorization"="Basic 1234--etc..."]))

[v-yuezhe-msft]

@webportal,

Add a new blank query in Power BI Desktop, then paste the following code in Advanced Editor. 

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.1/contacts", [Headers = [#"Content-Type"="application/json", #"Authorization"="Basic <your_access_token>"]]))
in 
     Source

Regards,

[webportal]

@v-yuezhe-msft, thanks. I get "wrong credentials, exactly as before.

[v-yuezhe-msft]

@webportal,

Do you add your API key value in the <your_access_token> part of the following code? 

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.1/contacts", [Headers = [#"Content-Type"="application/json", #"Authorization"="Basic <your_access_token>"]]))
in
     Source

In addition, what is the result when you add a new blank query and enter APIkeyName in the Advanced Editor as described in this similar thread?



Regards,
Lydia

[webportal]

@v-yuezhe-msft I sure did!

As to your question, I can't figure out how to add the APIKeyName in the Advanced Editor in the thread you refer. It's not there.

Can you elaborate how to do that?

This is exactly what I'm doing:

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.1/contacts", [Headers = [#"Content-Type"="application/json", #"Authorization"="Basic <my_API_KEY>"]]))
in 
     Source

The error message I get is: "It's only possible to specify a API Key when a API Key Name is supplied."

Thanks for your help!

[webportal]

Also tried the following code with the same error:

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/", [Headers = [#"Content-Type"="application/json", #"ApiKeyName"="Contacts", #"Authorization"="Basic *********************"]]))
in 
     Source

[webportal · Accepted solution]

Figured out I had to Base64 encode the API Key.

This worked so far with some limitations:

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Contacts", [Headers=[Authorization="Basic ***********", ContentType="application/json"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"CONTACT_ID", "SALUTATION", "FIRST_NAME", "LAST_NAME", "BACKGROUND", "IMAGE_URL", "DEFAULT_LINKED_ORGANISATION", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "CONTACTLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER", "ASSISTANT_NAME"}, {"CONTACT_ID", "SALUTATION", "FIRST_NAME", "LAST_NAME", "BACKGROUND", "IMAGE_URL", "DEFAULT_LINKED_ORGANISATION", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "CONTACTLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER", "ASSISTANT_NAME"})
in
    #"Expanded {0}"

[VictorBreelle]

Hi There, 

i tried to configurate a new blank request for extract data from Incwo, 

i validated it but nothing happen, how im suppose to send the request for grab the data ?

i used this request =

"let#(lf) Source = Json.Document(Web.Contents(""https://www.incwo.com/512***/contacts/8415791.xml"", [Headers = [#""Content-Type""=""application/json"", #""Authorization""=""Basic <512***>""]]))#(lf)in #(lf) source #(lf)"

Is correct request ?

Thanks 🙂