Power Query Web.Contents and the Power of APIs in Power BI
Updated: Oct 26
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 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:
https://api.chucknorris.io/jokes/randomUsing your browser to navigate to the URL, you will see a JSON response like the one below:
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.
Extracting a field's value from a JSON record - GitHub Gist
In the previous step, we received a record as a response, and among the record, we can find the "value" field which contains the actual joke; let's extract the value of the field “value.” To extract the value, we need to reference the step where we obtained a record and the field's name in square brackets.
You could also extract the value from the record in a single step by adding the field's name in square brackets directly after the Json.Document function.
Getting the different jokes’ categories - GitHub Gist
This is the same request as the previous step; in this case, we will create the step BaseURL that will hold, well, the base URL.
Also, we will start using the RelativePath parameter; this will avoid dataset refresh errors and come in handy when using various endpoints. The RelativePath parameter attaches a complementary part to the URL; consider it an “&.”
This way, your Web.Contents function will request the Base URL + the Relative Path = http://api.chucknorris.io/jokes/categories
I recommend you read the following blog post from Chris Webb about RelativePath.
The code will return the following list.
Requesting a joke from a specific category - GitHub Gist 1 & 2
We will now request a joke from a specific category. To do this, we will add the “Query” section, which will add a parameter to the API’s URL; what this does is attach a “?” followed by the query https://api.chucknorris.io/jokes/random?category=animal
For this step, I will present you with two different approaches. In the first one, the query parameters are included in the Web.Contents function while the second declares them in a previous step (Parameters.)
Any of the previous codes will return a similar response as the one shown below.
Creating our first function - GitHub Gist
Let’s transform the joke of a specific category into a function, allowing us to iterate through our list of categories to get a joke of every category.
To create a function we need to include the string ()=> at the very end of the code; within the parenthesis we need to include the parameter that we will pass to the function; in our case, it will be a category. Then, we need to replace the fixed, hard-coded category for the parameter.
Using the function to iterate through the categories table - GitHub Gist
Let's put together what we learned before. The goal is to iterate our function through the list of categories to get one joke for each category.
The first thing we need to do is transform the Categories list into a table by pressing the button “To Table” on the “Transform” ribbon.
Then, we must add a new column by “Invoque a custom Function” (green). Here, we need to choose the function we created (orange), and we will pass the value of every row every time under the “Column1” category.
We are iterating through the table, sending a request for every row and getting a Chuck Norris joke of every category specified in Column1.
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.
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.
WIX Contacts with pagination and authentication in headers - GitHub Gist
This M Query code is designed to retrieve contact data from the WIX REST API, incorporating authorization and pagination mechanisms to ensure a smooth data retrieval process.
The code begins by setting the base URL for the WIX API. It then defines the necessary authentication details, such as the API key, account ID, and site ID, which are required by the WIX API for secure access. These details are encapsulated within the query headers, ensuring that every API request is authenticated.
The main functionality revolves around the List.Generate function, a powerful M Query function, which acts as an iterative loop. This function is crucial for handling pagination. For every iteration, the code fetches up to 1,000 contacts, starting from an offset that increases by 1,000 for each subsequent request. The loop continues until no more contacts are returned, indicating that all contacts have been fetched. The final result is a list of all contacts retrieved from the WIX API.
It's worth noting that the code comments provide insights into specific sections, such as the structure of the API response, where only the "contacts" field is extracted from the returned data.
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."