cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Pull data from a REST API Authentication

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"

 

ultipro.png

 

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"

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: REST API and Authentication

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. 

 

q6.PNG

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Helper II
Helper II

REST API and Authentication

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

 

ultipro.png

 

 

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>

Highlighted
Resolver IV
Resolver IV

Re: Pull data from a REST API Authentication

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.

Dan Malagari
Consultant at Headspring
Highlighted
Helper II
Helper II

Re: Pull data from a REST API Authentication

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.  

Highlighted
Community Support
Community Support

Re: REST API and Authentication

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. 

 

q6.PNG

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper II
Helper II

Re: REST API and Authentication

 

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

 

Highlighted
Community Support
Community Support

Re: REST API and Authentication

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Pull data from a REST API Authentication

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

 

 

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors