Refresh Error from Service when connecting to Harvest API

[jpt1228]

Hello, I have been researching my issue but have not found a solution. This is the connection string sample to call the API.

https://help.getharvest.com/api-v1/authentication/authentication/http-basic/

I am able to use basic authorization and enter the URL and User Name as my Key. There is no password as I use a key.

Here is the query in the advanced editor. 

let
    Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/candidates/")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

 From there I do the expaning columns and transformations. When I publish to the service and attempt to refresh or schedule refresh I get an error that my credentials are invalid.

I have seen posts about connecting anonymously and creating a connection string with the endpoint and the key as a header. I have also seem where you can create a connection via the gateway to the web data source. Since there is no password when using a key this option doesn't work.

I am pretty new to connecting PBI to an API and the connection string syntax. Any suggestions with sample code not requiring a password but using the url and key as the credentials?

Thanks

[v-henryk-mstf]

Hi @jpt1228 ,

Here is a detailed blog description, I hope to help you solve the problem.Connecting to and using the REST API in Power BI - ERP Software Blog

Let me know the test result immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jpt1228]

hello @v-henryk-mstf I am not super technical and do not see how the blog post would translate to my issue. I am able to connect to the API and complete the call using the Power BI desktop. However, once I publish to the service and attempt to schedule a refresh I get a bad credential error.

From what I have researched I need to create the full connection string into the advanced editor and connect anonymously. 

let
    Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/candidates/ -u key123key123")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

[v-henryk-mstf]

Hi @jpt1228 ,

Here is a solution that is similar to your issue, I hope it can help you solve the problem.

Solved: Re: REST API Paging Power Query - Microsoft Power BI Community

If the problem is still not solved, can you provide a screenshot of the detailed operation steps so that I can find out the cause of the problem. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jpt1228]

Hello @v-henryk-mstf here is where I am stuck on the solution. I have a sample of my same issue with a solution but I am not sure how to change the api_key to [Headers=[AUTHORIZATION...

SAMPLE QUERY

(page as number) as table =>

let

  Source = Json.Document(Web.Contents("https://api.resumatorapi.com/v1/applicants/page/" & Number.ToText(page) & "?apikey= xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx")),

-----------------------------------------------------------------------------------------------------------------------------------

Converted to this query to publish to Power BI Service:

"Faking out"

Source = Json.Document(Web.Contents("https://api.resumatorapi.com/v1/applicants?apikey= xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",

    [RelativePath="page/" & Number.ToText(page),

    Query=[apikey=" xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]]))

MY QUERY:

(PageStart as text)=>

let

    Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?page="&PageStart&"&per_page=500", [Headers=[AUTHORIZATION="Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx="]])),

----------------------------------------------------------------------------------------------

I am not sure how to use the example which uses ?api_key=xxxx…”)) as the authentication and my instance that uses ,[Headers=[[AUTHENTICATION=”Basic xxxx…=”]]))