top of page
  • Writer's pictureOscar Martinez

Power Query Web.Contents and the Power of APIs in Power BI

Updated: Oct 26

API's by Midjourney
API's by Midjourney

Learning to consume APIs in Power BI has been a game-changer for me. As I started making my first API requests, a world of data/possibilities opened in front of my eyes; today, I am sharing in a condensed manner what I have learned about the Power Query Web.Contents APIs in Power BI.

This post aims to create a comprehensive walkthrough that will bring up to speed any newbies that have never consumed data from any APIs. I also hope that people with previous experience consuming data from APIs will find the key needed for developing more complex solutions.

What is an API?

API stands for Application Programming Interface. At its core, it's a set of rules and protocols that allows one software or program to interact with another. Think of it as a bridge between different software systems, allowing them to communicate with each other.

In the context of Power BI and Power Query's Web.Contents function, and APIs become especially powerful. They allow users to pull data from various sources into their Power BI dashboards directly. This enhances data visualisation and decision-making capabilities.

APIs are like translators or messengers, ensuring that different software systems understand and cooperate efficiently and effectively.

Web.Contents in Power Query as a way to leverage APIs in Power BI

Power BI provides multiple ways to connect to data sources, including external services through APIs. Power BI requires the API endpoint URL and any necessary authentication details when connecting to an API. Once connected, Power BI can retrieve data from the API and utilise it for data modelling and visualisation analysis. The connection to an API is typically established via the Power Query Editor in Power BI.

The Web.Contents function, part of the M language, is vital for retrieving web content, especially when working with APIs.

Web.Contents - PowerQuery M | Microsoft Learn

Our first API requests in Power BI starring Chuck Norris

Chuck Norris thinking about API's - Midjourney composition
Chuck Norris thinking about API's - Midjourney composition

Chuck Norris API is a great way to learn how to use APIs in Power BI because it provides an exciting and engaging example to work with. Plus, the API is well-documented and easy to use, making it an excellent choice for beginners. It will give you a solid foundation for working with other APIs in the future. Warning: The responses will contain profanity and bad-taste jokes.

Basic API request - GitHub Gist

We will start with a very basic API: Chuck Norris, which is a service that, every time it is called, answers a JSON record with a random Chuck Norris joke:

Using your browser to navigate to the URL, you will see a JSON response like the one below:

HTTP Request in browser
HTTP Request in the browser

To consume the response, we will wrap this URL with the Web.Contents function, which tells PQ the content comes from the Web and the Json.Document function, which tells the data will come in JSON format.

The code will return the following record.

Chuck API's response record
Chuck API's response record

Extracting a field's value from a JSON record - GitHub Gist

Getting the different jokes’ categories - GitHub Gist

Requesting a joke from a specific category - GitHub Gist 1 & 2

Creating our first function - GitHub Gist

Using the function to iterate through the categories table - GitHub Gist

Web.Contents Boilerplate

In coding, boilerplates are pre-made templates that developers can use to save time and hassle when starting a new software project. They come with different programming languages and frameworks, so you don't have to start from scratch every time. Plus, you can always customise them to fit your specific needs. They're super handy for making development faster and more efficient.

Today, 90% of the time, I start a new API request in Power Query with a boilerplate code, which I adapt to each specific request. You can find the text version of this code in this GitHub Gist.

Web.Contents for API Boiler plate
Web.Contents for API Boiler plate

Other API Examples

If you're interested in integrating Power BI with different APIs, explore the examples below that you may find helpful, and see how you can leverage the power of data to drive business success.

Gumroad API with pagination and authentication in query parameters - GitHub Gist

This M Query code for Power Query is designed to retrieve sales data from the Gumroad API. The central function, getSalesFx, requests sales information from the API. However, the API limits its response to 10 sales records per request. To overcome this limitation, the code employs pagination. If more sales data pages are available, the API provides a next_page_key in its response.

The main logic utilises the List.Generate function to iterate through these pages until no more data is left (i.e., a response with success = "FALSE"" is received). During each iteration, it checks if the next_page_key exists and, based on its presence, either fetches the next page of sales data or generates a success = "FALSE record to indicate the end of the data.

The final output is a consolidated list of all sales records from the API. It's crucial to note that this code contains a placeholder for the Gumroad API key, and the user is advised not to hardcode the key but to use secure practices like fetching it from Azure Key Vault.

Authentication is managed through the query parameter access_token.

Gumroad sales API with pagination
Gumroad sales API with pagination

WIX Contacts with pagination and authentication in headers - GitHub Gist

Wrapping up

As we wrap up this deep dive into the intricacies of APIs in Power Query and Power BI, I sincerely hope this guide is invaluable in your ongoing journey. The world of data and APIs offers endless possibilities, and with tools like Power Query at our disposal, there's so much we can achieve. Whether you're just starting or refining your skills, I trust that the insights shared here will be a beacon of knowledge, illuminating your path forward. Wishing you success and discovery in all your future endeavours with Power Query and Power BI."

bottom of page