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 Power Query Web.Contents and the Power of APIs in Power BI.
This post aims to create a comprehensive walkthrough of the function Web.Contents and APIs in Power BI 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 Web.Contents function and 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.
Want to know more about Web.Contents and Power Query in general? PowerQuery.How is a great place for it.
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.
01 Basic API request
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.
02 Extracting a field's value from a JSON record
03 Getting the different jokes’ categories
04 Requesting a joke from a specific category
05 Creating our first function
06 Using the function to iterate through the categories table
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.
M Copilot
This Power Query M designed to communicate with the OpenAI API to generate responses based on a given prompt. The function is part of a larger framework aimed at integrating AI-powered text generation into Power Query processes.
For this API you will need a paid OpenAI subscription; to know more: https://www.maisieai.com/help/how-to-get-an-openai-api-key-for-chatgpt
Gumroad - API with pagination and authentication in query parameters
WIX - Contacts with pagination and authentication in headers
Wise - List balances for a profile
Mailjet - Get full contact list
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."