Refreshing data from REST API

[Phoenixke]

Hi,

I've been using PowerBI Desktop to call on reports from Kronos Workforce Ready. They have a pretty straight-forward REST API (https://secure.workforceready.eu/ta/docs/rest/) and using the blank query I've been able to POST for a bearer token which is then used for GET-ing the reports.

So I can fetch the data that I need and reports to help our business and publish this to the service.

The issue I'm running into is when I want to refresh my dataset: the /login is entirely anonymous with credentials in the body of the POST and PBI Service refuses to connect to the endpoint (well, it connects but it uses GET instead of POST). I can refresh just fine in Desktop but Data Gateway keeps telling me:

Underlying error message:

Web.Contents failed to get contents from 'https://secure.workforceready.eu/ta/rest/v1/login' (405): Method Not Allowed

 I'm assuming this is because the Web doesn't allow for a body to do a POST? All the while permitting it in the blank query by using Content=Text.ToBinary() in the Web.Content().

Is there a way to automate this refresh of API data in any way other than scripting it outside of PBI?

[Anonymous]

Hi!, i need help 

i make this query on power bi

but i have this as an answer

[Anonymous]

Hi,

We have a number of clients that have the same situation so we built a custom solution that will make intergrating the workforce ready reports simple and easy.  They key for us was being able to use scheduled updates so we needed more than the posts and also wanted to minimize the number of web calls so we could refresh frequently throughout the day.  Feel free to ping me if you're interested.  christopher.keslin@lachesisllc.com 

Cheers,

--Chris

[roberthurd]

Hi Phoenixke. I'm also trying to implement REST API calls to Kornos WFR in Power BI.

I can query reports easily enough, but I'm struggling to get the Login API to work. I keep receiving a 400 Bad Request error.

Would you be kind enough to post a copy of your query?

Here's mine.

bodyLogin = "{""credentials"": {""username"":""MyUsername"",""password""=""MyPassword"",""company""=""123456""}}",


urlLogin = "https://secure.workforceready.eu/ta/rest/v1/login",

source = Json.Document(Web.Contents(urlLogin, [Headers = [#"Accept"="application/json",#"Content-Type"="application/json",#"api-key"="MyApiKey",#"x-forwarded-proto"="https",#"cache-control"="no-cache"], 
                                    Content = Text.ToBinary(bodyLogin)])),

[Phoenixke]

@roberthurd: I'm using parameters in mine but you can hardcode if you wish. This one works quite well for me:

let
    WFRtoken = "Bearer " & Json.Document(Web.Contents("https://secure.workforceready.eu/ta/rest", 
        [Headers=[#"Accept"="application/json", #"Content-Type"="application/json", #"Api-Key"="apikey"], 
        Content=Text.ToBinary("{""credentials"": {""username"": ""user"",""password"": ""pass"",""company"": ""shortname""}}"),
        RelativePath = "/v1/login"]))[token]
in
    WFRtoken

[kattlees]

We just started using Kronos and I have never used API before so am struggling to get us connected. Do you guys have any helpful hints for me or simple steps to show me how to get the connection set up? Kronos has sent me their API page and basically said "Figure it out". I have gotten a connection but only get a document so am not even sure I did that right.

[Phoenixke]

@kattlees Depends a bit what you're looking to do but the above should help you to call reports. It also depends on what product you're using from Kronos. Both Workforce Ready and Workforce Dimensions use API's but if I'm not mistaken Workforce Central uses something called WIM (they might have gone to API already, dunno). 

This one will give you back a token if you're using WFR, which is the start of everything:

let
    WFRtoken = "Bearer " & Json.Document(Web.Contents("https://secure.workforceready.eu/ta/rest", 
        [Headers=[#"Accept"="application/json", #"Content-Type"="application/json", #"Api-Key"="your_apikey_here"], 
        Content=Text.ToBinary("{""credentials"": {""username"": ""your_user_here"",""password"": ""your_pass_here"",""company"": ""your_shortname_here""}}"),
        RelativePath = "/v1/login"]))[token]
in
    WFRtoken

If you're in North America, you'll have to use secure.saashr.com instead of secure.workforceready.eu.

[roberthurd]

@Phoenixke that's really great. Your query works well. Thank you.

[v-yuezhe-msft · Accepted solution]

@Phoenixke,

When your API requires the use of POST with specific headers to return a successful response, it doesn't support to refresh the dataset in Power BI Service, please vote up the following idea.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/19033174-scheduled-refresh-should...

Regards,
Lydia

[Phoenixke]

@v-yuezhe-msft: thank you and upvoted