Power BI: A story of Ice,Fire and JSON Files (Part 1)

Like most people, I’ve been having major withdrawal symptoms from Game of Thrones (GoT) recently and was left wondering what to do until next year to get my GoT fix!

After watching a few YouTube theory videos and restarting the series from season one, I started to
think (being the geek I am) how cool it would be to have a Power BI report specifically for GoT, so I decided to make one!

In this series of blog posts, I’ll show you how you can import & manipulate JSON content from web API data sources, use functions to create multiple queries and connect disjointed information to present meaningful data.

That’s what I do, I Use BI and I Know Things…

To set upon my mission to create a Power BI report for GoT and we’ll need a data source.

After a bit of googling I found a great site called AnAPIofIceandFire.com this was a open API database containing information from A Song of Ice and Fire. We’ll use this data to create the report.

Importing JSON Data to Power BI

To begin will start off importing the Houses into BI, the website has excellent information on how to do this and we can view the information via the following URL: http://www.anapioficeandfire.com/api/houses

1. Go to Get Data > Web

2. Paste the URL into the URL section and click OK.

 3. This imports data and automatically detects its a JSON file but notice we have no readable information yet 🙁

4. We can use the convert to table option to convert and then expand the table to view the content.

Note: Notice I only got 10 results back, this is because by default the header only returns 10, to check this, I added ?page=1&pageSize=1000. This returned a maximum of 50 Houses which obviously isn’t enough houses.

In the next post we’ll look at creating a function with a parameter to specify the page in the URL to get other results.

Leave a Reply

Your email address will not be published. Required fields are marked *