asked on

How to Create a Web API Call in Power BI

Need help creating an API call in Power BI to retrieve data from a specific vendor.
They provided the following link:

https://www.esayworkmobile.co.uk/wmbapi3/workmobileintegration.asmx

I previously was able to create a working API call using Power BI to a different vendor.
As follows:

https://training.reliaslearning.com/webservices/elapi.asmx/ExportEnrollments?authorizationID=USERNAME&passcode=PASSWORD&OrganizationID=ORGID&DateRangeType=3&startDate=01/01/2018&endDate=04/03/2028

Note: Username, Password and OrgID were substituted out in the above example.

While there are several way to retrieve this data, we want a consistent method. We would like to use an API similar to the above to retrieve any data table into Power BI.

MAC

calebbaity

ASKER

What I have found so far:

Looking at the API call that did work:

https://training.reliaslearning.com/webservices/elapi.asmx/ExportEnrollments?authorizationID=USERNAME&passcode=PASSWORD&OrganizationID=ORGID&DateRangeType=3&startDate=01/01/2018&endDate=04/03/2028

And the provided SOAP 1.2 documentation:

POST /webservices/elapi.asmx HTTP/1.1
Host: training.reliaslearning.com
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <ExportEnrollments xmlns="WebServices/elapi.asmx">
      <authorizationID>string</authorizationID>
      <passcode>string</passcode>
      <OrganizationID>int</OrganizationID>
      <DateRangeType>int</DateRangeType>
      <startDate>string</startDate>
      <endDate>string</endDate>
    </ExportEnrollments>
  </soap12:Body>
</soap12:Envelope>
HTTP/1.1 200 OK
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <ExportEnrollmentsResponse xmlns="WebServices/elapi.asmx">
      <ExportEnrollmentsResult>
        <xsd:schema>schema</xsd:schema>xml</ExportEnrollmentsResult>
    </ExportEnrollmentsResponse>
  </soap12:Body>
</soap12:Envelope>

Open in new window

I can see that the values for the API call are coming from the lines of code above as follows:

The address is being pulled from lines 2 and 9
while the parameters are being pulled from lines 10-15

However, the current vendor's SOAP 1.2 documentation is more complex.

POST /wmbapi3/workmobileintegration.asmx HTTP/1.1
Host: www.esayworkmobile.co.uk
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <RetrieveAllData xmlns="https://www.esayworkmobile.co.uk/">
      <i_objUForm UserFormId="int" AutoUpload="boolean" AllowSaveToDrafts="boolean" Sys_Archived="boolean" Sys_CreatedDateTime="string" Sys_CreatedUserName="string" Sys_ModifDateTime="string" Sys_ModifUserName="string" Name="string" Description="string" TableName="string" UserId="int" Live="boolean" MobileVisible="boolean" LocationAware="boolean" />
      <credential>
        <FormsSchemaId>string</FormsSchemaId>
        <CustomerId>int</CustomerId>
        <LoginId>int</LoginId>
        <Password>string</Password>
        <UserLogin>string</UserLogin>
        <InterfaceType>V1_Deprecated or V2</InterfaceType>
      </credential>
      <markAsDownloaded>boolean</markAsDownloaded>
    </RetrieveAllData>
  </soap12:Body>
</soap12:Envelope>

Open in new window

I would think that the address would be from line 2 and 9
as:

https://www.esayworkmobile.co.uk/

And the parameters would be from lines 10-18.
But, I am not certain how to render the parameters.

Currently, I am looking at the Advanced editor in Power BI and using Power Query M to create the
needed parameters so that I can import this data.

Again any help with using the advanced editor or using M would be very much appreciated.

Thanks in Advance!

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.