1. Insightly Help Center
  2. Community
  3. Insightly API
Avatar
Alistair Marr
Follow

Power BI Reporting

Really struggling with and i hope someone can help me.

I already run a number of reports using PowerBI by exporting from Insightly into excel, and then into PowerBI,

What i would really like to be able to do is create some realtime reports, nothing complex, just things along the lines of how we are doing against target etc. 

I remember there used to be a connector to PowerBI service, but I would like to utilize the API and link in with PowerBI Desktop, but what ever i try (which at this point is probably everything other than the actual answer) i can't seem to get the setup right.  

I've tried putting the username as my ApiKey (Base-64 Encoded), I've tried adding it in through Headers,

Am i just barking up the wrong tree here, and this is not possible?

Cheers for any help

Ali

1

Comments

4 comments
Date Votes
  • Avatar
    Andrés Santamaria - Federal Masonry
    (Edited )

    Ok, I always wanted to do this and never had the time, so your question led me to try and it worked. 

    This is how I did it. I am not an expert on PowerBI nor PowerQuery nor whatever, so I may have taken the long way, but worked. 

    I'm aware your question is about reports not pulling data, but since your question is related to authentication, well, I think the approach works since the endpoints are pretty much the same. Right?

    Short version: I used the API key without encoding as the user name and blank as password.

    Long version: 

    1. Open Power BI Desktop
    2. Get Data -> Blank Query
    3. Click Advanced Editor on the ribbon
    4. Paste the query text below on the formula bar and Done
    5. Below the formula bar, a yellow bar appears that says "please specify how to connect" and an Edit Credentials button
    6. On the Credentials dialog box select Basic, API key on User name, nothing on Password and Select which level to apply those credentials I selected the most general/broader URL.
    7. Press Connect

    That's it.

    This is the query I used.  It is ready for parametrization that's why it is so long, but it is a valid query.

    let
    Source = Table.FromRecords( Json.Document
    (Web.Contents("https://api.na1.insightly.com/v3.1/Contacts?" 
    & "brief=" & Text.From("false") 
    & "&skip=" & Text.From(923)
    & "&top=" & Text.From(21)
    & "&count_total=false"
    , [#"Headers" = [#"accept"="text/xml", #"Accept-Encoding"="gzip", #"content-type"="text/xml" ] ]
    ) 
    )

    )
    in
    Source

    Hope it helps.

     

    1
    Comment actions Permalink
  • Avatar
    Alistair Marr

    Absolutely amazing,

    Worked first time, I've been battling with that off and on over the last year, but never thought to try the unencoded API Key.

    Thank You

    0
    Comment actions Permalink
  • Avatar
    Andrés Santamaria - Federal Masonry

    Glad to hear.  I did try in the past many combinations and never worked so you were not alone. 

    The problem still persists for long queries because pagination cannot be accomplished without a custom connector (that allows access to the response headers) so still some way to go before becomes useful to me. 

    1
    Comment actions Permalink
  • Avatar
    Alistair Marr

    I'm trying to get the data live, I want my guys to be able to login to PowerBI and get up to date reports... but I think I'm getting somewhere now.

    Seems a useful feature to me, enabling DirectQuery for the API would be very useful about now ;-)

    0
    Comment actions Permalink

Please sign in to leave a comment.