Pull data from a REST API Authentication

[surfersamu]

I'm trying to pull data from REST API. At the moment I'm facing some difficulties tyring to authenticate.

All I have is:

Link:

https://service4.ultipro.com/services/EmployeePerson 

Authorization Headers:

"Authorization: Basic c29tZXVzZXI6c29tZXBhc3dvcmQ="

"US-Customer-Api-Key: ABCDE"

I get this Error when I click OK:

Details: "The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, If-Modified-Since, Prefer, Referer"

[ZappySys]

ZappySys has introduced powerful API Drivers to query virtually any JSON / XML API services in Power BI (e.g. Zendesk, Twitter, Google API, Amazon AWS)

Check this blog for more info:  https://zappysys.com/blog/howto-import-json-rest-api-power-bi/

Basically, Calling REST API in Power BI can be a challenging process because you have to deal with many complex issues. If its simple JSON / XML then fine but in the real world, many other issues arise such as pagination, flatten the hierarchy, error handling, security (OAuth, Basic, Certificate). 

Hope this helps!!

Step-1 : Create your ODBC DSN to call REST API or read JSON / XML dataStep-2 : Build / Test your REST API QueryStep-3: Import REST API data in Power BI using ODBC connection (Under Get Data >> More >> Other)

You can write queries like below (JSON Driver Example)

SELECT * FROM $
WITH(
src='https://service4.ultipro.com/services/EmployeePerson'
,Header='Authorization: Basic c29tZXVzZXI6c29tZXBhc3dvcmQ= || US-Customer-Api-Key: ABCDE'
,RequestMethod='GET'
)

[malagari]

I don't think this is a Power BI issue; the details that are returned are most likely from the API for ultipro.com.  You can try connecting with a tool like Postman to verify that the issue is in the API information.  If you're able to connect via Postman, then it is something isolated to Power BI.

[surfersamu]

You are right. I tried on postman and got nothing. I have started a support ticket with them.

Assuming the API wasn't giving me an error. Is that the right way to make a HTTP GET Request? Sorry I'm new to REST APIs.  

[surfersamu]

Hi guys,

I am tyring to get data from UtliPro REST API. The problem that I'm facing is that I don't know to how to authenticate with http headers.

This is what I have:

Link

https://service4.ultipro.com/services/EmployeePerson 

HTTP Headers

Authorization:Basic FAKECREDSFUlZJQ0U6RzBUUzNhczBuMjAxNyE=

US-Customer-Api-Key:ABCDE

When I try this on the Get Data from Web dialog I get:

"The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, If-Modified-Since, Prefer, Referer"

This is an example provided by UltiPro using XML:

<s:Envelope xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">

<s:Header>

  <a:Action s:mustUnderstand="1">http://www.ultipro.com/services/employeeemploymentinformation/IEmployeeEmploymentInformation/GetEmpl...</a:Action>

  <UltiProToken xmlns="http://www.ultimatesoftware.com/foundation/authentication/ultiprotoken">27e17fe3-29cb-48a0-a130-4bad41971dcb</UltiProToken>

  <ClientAccessKey xmlns="http://www.ultimatesoftware.com/foundation/authentication/clientaccesskey">CAN12</ClientAccessKey>

 </s:Header>

 <s:Body>

 <GetEmploymentInformationByEmployeeIdentifier xmlns="http://www.ultipro.com/services/employeeemploymentinformation">

  <employeeIdentifier xmlns:b="http://www.ultipro.com/contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:type="b:EmployeeNumberIdentifier">

  <b:CompanyCode>C0014</b:CompanyCode>

  <b:EmployeeNumber>004446049</b:EmployeeNumber>

  </employeeIdentifier>

 </GetEmploymentInformationByEmployeeIdentifier>

 </s:Body>

 </s:Envelope>

[v-qiuyu-msft · Accepted solution]

Hi @surfersamu,

I have tested in Power BI desktop using the connection information you provided, there is no error throws out. After you fill the connection information like the image you provided and click OK button, please use Anonymous authentication, as you already provide the authorization key. 

You can download the attached pbix file to have a look. By the way, here are some samples of calling API to get data in Power BI: 

Calling REST APIs and Parsing JSON made simple with Power BI

Get Data from Twitter API with Power Query

Best Regards,
Qiuyun Yu 

[surfersamu]

UltiPro support takes a very long time to reply.

Ok I found the reason why is not working. Because UltiPro requires you to get a token to make a SOAP request. I have a token now.

Using advanced editor how can I fix this code:

I keep getting:

DataSource.Error: Web.Contents failed to get contents from 'https://service4.ultipro.com/services/EmployeePerson' (415): Unsupported Media Type

let
 Source = Xml.Tables(Web.Contents("https://service4.ultipro.com/services/EmployeePerson"
 ,[Content=Text.ToBinary("<s:Envelope xmlns:a="&Character.FromNumber(34)&"http://www.w3.org/2005/08/addressing"&Character.FromNumber(34)&" xmlns:s="&Character.FromNumber(34)&"http://www.w3.org/2003/05/soap-envelope"&Character.FromNumber(34)&">
 <s:Header>
 <a:Action s:mustUnderstand="&Character.FromNumber(34)&"1"&Character.FromNumber(34)&">http://www.ultipro.com/services/employeeperson/IEmployeePerson/FindPeople</a:Action> 
 <UltiProToken xmlns="&Character.FromNumber(34)&"http://www.ultimatesoftware.com/foundation/authentication/ultiprotoken"&Character.FromNumber(34)&">6ddeadsfdsfdsfsdfdsfdsfds6</UltiProToken> 
 <ClientAccessKey xmlns="&Character.FromNumber(34)&"http://www.ultimatesoftware.com/foundation/authentication/clientaccesskey"&Character.FromNumber(34)&">ABCD</ClientAccessKey> 
 </s:Header>
 <s:Body>
 <FindPeople xmlns="&Character.FromNumber(34)&"http://www.ultipro.com/services/employeeperson"&Character.FromNumber(34)&">
 <query xmlns:b="&Character.FromNumber(34)&"http://www.ultipro.com/contracts"&Character.FromNumber(34)&" xmlns:i="&Character.FromNumber(34)&"http://www.w3.org/2001/XMLSchema-instance"&Character.FromNumber(34)&">
 <b:CompanyCode /> 
 <b:CompanyName /> 
 <b:Country /> 
 <b:EmployeeNumber /> 
 <b:FirstName>like (a%)</b:FirstName> 
 <b:FormerName /> 
 <b:FullOrPartTime /> 
 <b:Job /> 
 <b:LastHire /> 
 <b:LastName /> 
 <b:Location /> 
 <b:OrganizationLevel1 /> 
 <b:OrganizationLevel2 /> 
 <b:OrganizationLevel3 /> 
 <b:OrganizationLevel4 /> 
 <b:OriginalHire /> 
 <b:PageNumber /> 
 <b:PageSize /> 
 <b:PayGroup /> 
 <b:Status /> 
 <b:SupervisorLastName /> 
 <b:TerminationDate /> 
 <b:TimeClockId /> 
 </query>
 </FindPeople>
 </s:Body>
 </s:Envelope>"),Headers=[#"Accept-Encoding"="gzip,deflate", #"Accept"="Basic U0VfdasdasfdasMjAxNyE=", #"Content-Type"="text/xml;charset=UTF-8"]]
))
in
Source

[v-qiuyu-msft]

Hi @surfersamu,

It seems your original issue is solved, right? 

Regarding the second issue, if you are not familiar with the Power Query, please use features available in Query Editor UI to transform data. As this is another issue, please post in a new thread and share the pbix file if possible. 

Best Regards,
Qiuyun Yu