cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
surfersamu
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

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
ZappySys
Helper I
Helper I

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'
)

 

 

 

malagari
Resolver IV
Resolver IV

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

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
Helper II
Helper II

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>

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

 

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

 

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors