For Day 3 of Dashboard Week. Using Power BI with The Solar System OpenData.
What is an API? In the simplest term, and API a way for you to access a database through a URL – if you want an in-depth explanation click here.
Now that you understand what an API is, let’s go through the steps of taking us from an API URL, to a fully visual dashboard.
Phase I: Download the data from the API using Power BI
1 | Get Data
First of all let’s obtain the data from the API. Click on Get Data.
2 | Select Web
The from the screen that pops-up select Other then Web.
3 | Type in API URL
Then copy and paste the URL in the URL text box.
You may also see another screen where it will ask you to select what kind of web connection you would like to make. I chose anonymous connection.
Phase II: Explore the Data
Now we have imported the API, you should see this:
If we click on Bodies or on the Grey area in the Row it will show the list of records in the bottom.
However if we click on the list text it will show all records like this:
Then if I click on the Record text it will open this:
Here you can see the individual details for each record.
If you quickly want to see the data in the records I would reccommend just clicking on the grey area in the rows:
You can navigate back and forward in time based on the changes you have made using the Query Settings pane on the right. You can show/hide this in the View tab by clicking Query Settings.
At this point the data isnt ready to use, and if we load into Power BI it looks like this:
We need to prepare the data for use. Let’s go back to the edit query window and transform the data. Click the dots to the side of the Data source name and click Edit query:
Phase III – Transform the Data
Now we understand how the data is structured, and what the API is returning us, we can prepare the data for use.
1 | Convert Source to Table
Converting the data source to a table will allow us to transform it. Click on the Transform tab and click To Table. With this API we can use ‘None’ as the delimiter.
Note, they we can use the tools in the transform and column tabs that were previously greyed out:
2 | Transform the Table
For this API, to use the data we need to expand the columns (as the colums are kept within each record). Click the button in the top right highligther in red below to expand the columns.
I’ve unchecked ‘use original column name as prefix’ becasue that would return column1.id and I just want id as the column name.
Expanding the columns gives us our completed table (don’t forget to do this for the other columns too)!
If you want to navigate back, or delete a step you’ve made, you can do that on the right hand side.
Depending on your data set you may need to use other tools.
Now we can use the home, transform, and add column tabs to clean and prepare the data as you would like – creating additional calculations or removing data you dont need.
You can also remove any columns you decide not to use (remember you can also use the navigate pane to undo those actions, or view the table in a previous state).
Don’t forget to save!
Phase IV – Clean/Edit Data
Now the data is ready to use, however we can do additional transformations and calculations to find other information within the data.
One exmaple is our mass value and mass exponent columns.
If we want to show mass in an axis, it’s probably better if it is a single column of the actual mass in kgs.
We need to calculate:
[ value ] * 10 ^ [ exponent ].
Number.Power is the function to do this in Power BI:
We will now have a new column named Mass with the total mass in KG for each object.
We can now do this for the volume too.
If needed you can click on the button to the left of the column name (which usually shows ABC 123) to change it to a different data type (ie. decimal, int, etc).
If you want to edit a formula, click the cog icon to the right of the event in the navigation pane.
After some calculations and cleaning of the data I ended up with a data set that would help me explore when objects were discovered in our solar system.
Now save, you can exit the query editor, and make sure to apply changes when prompted!
Phase V – Visualise
1 | Understand what story/information you want to share with the user
This is the most important step as it will help you move in the right direction and avoid wasting time. This step is also restricted by the data that is present.
For example I can’t use this specific data to share the kinds of elements present in each planet’s atmosphere.
Through exploring the data (and the idea that drove you to use the data source in the first place) you can find what a story that is interesting, valid, and possible with the data source.
Through exploring the data I already knew that I was going to look at the discovery of objects in the solar system.
2 | Create basic charts
Discovered by and Count of Names (aka Count of Planets.) This gave me a chart of how many planets each person had discovered.
To achieve this I dragged DiscoveredBy and Count of Names into a chart, and selected the bar chart option.
Then I created another on to look at the Discovered Date and the Count of Planets:
3 | Combine multiple charts into a cohesive dashboard
In the end I didn’t use the first chart – which is okay!
See the finished result below:
There you have it, 5 easy steps to create a visualisation from an API in Power BI. It’s super easy to learn, and if you follow along with the steps one by one you should be able to learn the basics in one sitting.
If you get stuck (which can happen alot when making calculations in custom columns) you search the web and find plenty of answers to solve the issue you are running into.