What is SOAP API?

Before diving into the nitty-gritty, let’s talk about SOAP API. SOAP, or Simple Object Access Protocol, is a protocol for exchanging structured information in the implementation of web services. Unlike REST APIs, which are more commonly used these days, SOAP APIs are a bit old-school but still widely used in enterprise settings. They are known for strong typing, strict security, and ACID-compliant transactions.

Two Ways to Integrate SOAP APIs with Power BI

There are generally two ways to integrate SOAP APIs with Power BI:

1. Using M Language inside Power Query: This is the built-in way to fetch data into Power BI, but it can be a bit limited in terms of what you can do with the data once it’s fetched.

2. Using Python: This is my preferred method, as Python offers a lot more flexibility in terms of data manipulation and transformation.

Prerequisites

• Administrative access to the SOAP API

• Basic understanding of SOAP APIs and XML

• Familiarity with Python

• Power BI Desktop

• Python 3.x installed

Note on Power BI and Python

Power BI integrates with Python through pandas DataFrames. So, make sure to convert your fetched data into a DataFrame for a smooth transition into Power BI.

Step-by-Step Guide

Absolutely, feel free to check out the full code on my GitHub profile.

https://github.com/RamaDabbeet/SOAP-API-Country-Information-Retrieval

In many cases, SOAP APIs require a session key for authentication. This key usually expires after a specific period. Therefore, the first part of creating a solid Python script for Power BI integration is to generate a session key. This key then becomes a variable in the second part of the script, ensuring that every time you refresh your Power BI dashboard, a new session key is generated.

Step 1: Session Key Generator Script

Open your terminal and run:

• pip install requests pandas

And now, this is what we have to do:

Initiate the Request: The script starts by sending an HTTP POST request to the specified API endpoint. The session key, crucial for authentication, is included in the request body.

Check the Response: Once the API responds, the script checks the HTTP status code to confirm if the request was successful.

Parse the Data: If successful, the script parses the XML response to extract the ‘ReturnCode’, which tells us the outcome of the operation.

DataFrame Creation: Lastly, the script converts this extracted data into a pandas DataFrame. This makes it Power BI-friendly and ready for your dashboards.

For a detailed look, please check the script below.

Step 2: Data Fetching Script

Now that you have your Python script ready, it’s time to bring it into Power BI and see your data come to life.

1. Open Power BI Desktop: Launch Power BI and go to the ‘Home’ tab.

2. Get Data: Click on the ‘Get Data’ option and select ‘Python script’.

3. Paste the the whole Script: A window will appear where you can paste your Python script. Paste the script you’ve prepared.

4. Run: Click ‘OK’ to run the script. Power BI will execute the Python script and load the data into a new table.

5. Visualise: Now you can drag and drop fields to create your visualisations as you normally would.

And there you have it! You’ve successfully integrated a SOAP API into Power BI using Python. Every time you refresh your Power BI report, a new session key will be generated, ensuring your data is always up-to-date.

Step 3: Importing the Script into Power BI

Integrating SOAP APIs with Power BI doesn’t have to be a headache. With a bit of Python knowledge and this guide, you’ll be on your way to creating some impressive visualisations from any SOAP API. Whether you’re dealing with ever-changing session keys or just want a more straightforward way straightforward way to fetch your data, this guide has got you covered.

Conclusion