cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markholland Regular Visitor
Regular Visitor

Connect to an API with Power BI Desktop - Harvest

Hi,

 

I'm looking for some help connecting to an API, namely for Harvest. I've never connected a PBIX file to an API before and I'm having real trouble understanding why I can't connect. I'm not particularly technical so I'm hoping someone can explain this to me in simple language that I'll understand.

 

I've gone on to the Harvest API Documentation site and found the URL I want to connect to:

 

https://api.harvestapp.com/v2/time_entries

 

We're created a token using the Personal Access Token section of the Harvest Developers site.

 

I've structured the URL in the following way:

 

https://api.harvestapp.com/v2/time_entries/COMPANYNAME?access_token=TOKENDETAILS&account_id=ACCOUNTID

 

Firstly, is this the correct structure? The Time Entries API has all the fields I need so I didn't think I needed to list the fields but if that's incorrect, can you let me know what I'd need to do to pull these in.

 

If this is the correct structure, I'm then coming across an issue with I choose the Get Data > Web option in my PBIX file. From the 5 options that appear it seems like I should choose Web API, which then asks me for a Key but I don't know what this is and I can't see one generated from the Personal Access Token in Harvest.

 

Where am I going wrong? The previous post about connecting to the Harvest API contained a link to connecting to the Twitter API, but this just confused me even further. Like I mentioned earlier I'm not particularly technical when it comes to this stuff.

 

PLEASE HELP!

 

Thanks,

Marki

7 REPLIES 7
markholland Regular Visitor
Regular Visitor

Connect to an API in Desktop - Harvest

Hi,

 

I'm looking for some help connecting to an API, namely for Harvest. I've never connected a PBIX file to an API before and I'm having real trouble understanding why I can't connect. I'm not particularly technical so I'm hoping someone can explain this to me in simple language that I'll understand.

 

I've gone on to the Harvest API Documentation site and found the URL I want to connect to:

 

https://api.harvestapp.com/v2/time_entries

 

We're created a token using the Personal Access Token section of the Harvest Developers site.

 

I've structured the URL in the following way:

 

https://api.harvestapp.com/v2/time_entries/COMPANYNAME?access_token=TOKENDETAILS&account_id=ACCOUNTID

 

Firstly, is this the correct structure? The Time Entries API has all the fields I need so I didn't think I needed to list the fields but if that's incorrect, can you let me know what I'd need to do to pull these in.

 

If this is the correct structure, I'm then coming across an issue with I choose the Get Data > Web option in my PBIX file. From the 5 options that appear it seems like I should choose Web API, which then asks me for a Key but I don't know what this is and I can't see one generated from the Personal Access Token in Harvest.

 

Where am I going wrong? The previous post about connecting to the Harvest API contained a link to connecting to the Twitter API, but this just confused me even further. Like I mentioned earlier I'm not particularly technical when it comes to this stuff.

 

PLEASE HELP!

Community Support Team
Community Support Team

Re: Connect to an API with Power BI Desktop - Harvest

Hi Marki,

 

The Access Token should be added in the Http headers. Please try it as below. There is a wonderful example here. Since these headers aren't standard headers, please follow the example to add the code directly in the Advanced Editor.

let
     apiUrl = "https://api.harvestapp.com/api/v2/users/me",
     options = [Headers =[#"User-Agent"="Harvest API Example",#"Harvest-Account-ID"="8****8", #"Authorization"="Bearer 1***************Q"]],
     result = Web.Contents(apiUrl , options)
in
result

Note: Don't forget the blue bold part in the code.

Connect_to_an_API_with_Power_BI_Desktop_Harvest

 

Best Regards,

Dale 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
markholland Regular Visitor
Regular Visitor

Re: Connect to an API with Power BI Desktop - Harvest

Thanks @v-jiascu-msft,

 

I was able to get access by removing one of the backslashes from the URL and choosing 'Anonymous' from the Web options, as the access token was already in the URL.

 

The new URL looks like this:

 

https://api.harvestapp.com/v2/time_entries?access_token=TOKENDETAILS&account_id=ACCOUNTID

 

I removed the backslash after 'time_entries' and removed 'COMPANYNAME' which gave me access to all my records.

 

Now I just need to figure out to how extract all pages from the API. Any thoughts?

 

Mark

ryank Frequent Visitor
Frequent Visitor

Re: Connect to an API with Power BI Desktop - Harvest

Im actually going through this same import right now too. 

 

It looks like you can extract 100 time entries at a time (see pic). Have to figure out a way to get it extracting the rest of the pages if anyone has any ideas

 

Capture.JPG

markholland Regular Visitor
Regular Visitor

Re: Connect to an API with Power BI Desktop - Harvest

Hi @ryank,

 

I was able to extract all pages. The only issue is it then removes the ability to schedule a refresh on the Service. So if you're only going to use it in Desktop, you'll be ok.

 

I followed this post. There's a comment by @ImkeF that explains how it should appear in Advanced Editor:

 

https://community.powerbi.com/t5/Desktop/how-to-create-a-query-that-paginates/td-p/20047/page/3

 

I'm still stuck on how to get the Power BI to schedule a refresh when there's pagination in the coding.

 

Mark

 

 

fmr5000 Frequent Visitor
Frequent Visitor

Re: Connect to an API with Power BI Desktop - Harvest

@markholland any luck? I am attempting to do the same in PowerBI with harvest and a scheduled refresh. Please let me know.

 

Thanks!

markholland Regular Visitor
Regular Visitor

Re: Connect to an API with Power BI Desktop - Harvest

Hi @fmr5000,

 

I had to do a bit of a hack to work around it. I created a dataset for each page I needed, then appended them into one query. This worked for me as I'm looking for a rolling view. I was sent a solution by @ImkeF on the following thread but I've not had a chance to try it out:

 

http://community.powerbi.com/t5/Desktop/Scheduled-refresh-and-pagination-can-t-refresh-query/m-p/363...

 

There's also a webinar on the following blog that tries to do the same thing but again I've not had a chance to try it out:

 

https://datachant.com/2018/02/22/powerbi_jedi_starwars/?mc_cid=d61cd2e79c&mc_eid=4143af331f

 

Let me know if either of these work for you.

 

Mark