One of our Friday Challenges at the Data School was to connect to an API in Power BI and design a Power BI report using the data we extracted. We had the choice to connect to any API of our choosing with or without authorization or pagination. The API I chose to connect to was The New York Times Books API.
What is authorization and pagination?
Authorization is the security behind the API, which normally comes in the form of an API Key. Not all APIs require one and not all keys are easily obtained. In my case, this required creating an account with The New York Times Developer site and creating an API key which would allow me to access the API.
Pagination on the other hand is how the data is stored, as in there may be one page with all the data or you might need to call multiple pages to get all the tables of data. For the API I connected to, there was no pagination.
Connecting to the API
The Endpoint: /lists/overview.json
Parameter: published_date
Now instead of calling the API manually for every week of data, I created a table with 5 weeks of dates and created an API calling function. To create the API calling function I had to create a blank query with the following code:
= Json.Document(Web.Contents(
“https://api.nytimes.com/svc/books/v3/lists/overview.json?”
& “api-key=MyKey”
& “&published_date=2025-05-04”))
With this I now had a function that I could use in my table with the dates, I used a “Invoke custom function” in the create a new column which would call on the API using the date provided in the table for the “published_date” this would mean that 5 weeks of data could be called into the table without manually changing the published_date every single time. Next, I could then expand the column to get the data.
Building the Dashboard
The data was rich with information, there was plenty that could be done. I decided to focus on the data that I would like to see as a reader not as a publisher or any other audience; however, this kind of information was available.
As a reader what did I want to see? If I look at a specific book list I wanted to see which books are most popular, which author is popular, where can I review the book, what is the story about, what is the genre, where can I get the book, how much does it cost?
These were the kinds of questions I asked myself as I constructed this report. Now, I can see books by rank, how the rank moves across time, I can search for books by genre and I have the Author Spolight for every Week and list so that I can identify authors that might be worth my attention when looking for good books to read.
Final Thoughts
This was an enjoyable dataset to work with, as do enjoy reading a lot and it was interesting to see the trends of actual books and authors that I was familiar with and how they were doing against other books and authors. After this challenge, I have been inspired by the fun of connecting to APIs and I have attempted to connect to various other APIs to gather supplementary data for my other reports and dashboards and I will continue to do so in the future.
Without further ado here is my report and if you’re interested in finding a good book to read you can give it a go by clicking below to interact with my report.
