Easily visualize your data in Microsoft Power BI
It’s good to have data collected, but it’s hard to make sense of rows of data in a spreadsheet, right? That’s why today I will show you how easy it is to visualize data in Microsoft Power BI.
We’ll be using the desktop version of Power BI, so in case you don’t have it set up, please go to this page to download the installer. Do keep in mind that you need to create an account using your work or school email. For more on sign-up to Power BI, please refer to their page on the matter. For the dataset, you can download it from here.
About the data
Now that you have Power BI and the dataset in your computer, let’s look into the data. This dataset contains internet speeds I’ve collected at home from January 1st 2019 to March 31st 2019, once a day, using Python along with the speedtest-cli and pandas libraries (to perform the internet speed tests and to write the results to a CSV file, respectively). A link to the code is available at the end of this article.
As you can see in the following sample screenshot from Power BI,
our dataset has four columns:
- Date: the date of the test
- Ping (ms): the ping, collected in miliseconds
- Download (Mb/s): the download speed, collected in megabits per second
- Upload (Mb/s): the upload speed, collected in megabits per second
Pretty straightforward. And now for the dashboard we’ll create:
We’ll create a dashboard to visualize the average of each speed metric per day of the month, which will include line charts to see the evolution over the course of the days (1–31) and gauges for a quick look at the performance of each metric.
The intent of this dashboard is to create visualizations of the evolution and performance of the internet speeds collected, with the least amount of effort. To create this dashboard in Power BI, it’s pretty much a matter of adding line charts and gauges to the canvas and then dragging and dropping the data into each visualization.
Load the data into Power BI
Let’s start creating our dashboard. The first thing we’ll need is to load the dataset.
For that, open Power BI and then, in the Home tab of the ribbon bar (the top menu bar), click the Get Data dropdown and choose the Text/CSV option.
This will prompt you for a text (.txt) or CSV (.csv) file, i.e., the dataset you want to load. Choose the internet_speeds.csv file you have downloaded before.
In the following screen, we can preview the chosen dataset and either choose to load the data as is or modify it before loading it into the report. Since the data has been prepared beforehand, we only need to load it.
If we turn our attention to the Fields pane, we can see the internet_speeds dataset and its columns have been loaded.
By default, because the “Date” column has values of type date, Power BI has created a hierarchy to separate each component of a date (year, quarter, month and day). We can either use each component separately or use the complete “Date”.
Now that we have our data loaded, we can start working on the dashboard!
Create the line charts
To create visualizations, we use the Visualizations pane.
The top half shows the available visualizations and that’s where we go to add new visualizations to the canvas. As you can see, Power BI comes with a lot of visualizations and it’s possible to get even more from the Marketplace. The bottom half, we’ll get to it in a second.
Then let’s create our first visualization. Clicking the line chart options creates, as expected, a new empty line chart on the canvas like so:
Before adding data to the visualization let me just clarify the purpose of the bottom half of the Visualizations pane. When you click on a visualization on your canvas, you can then access various options and settings for it, separated into three main categories: Fields, Format and Analytics. The first category is about the data used in the visualization, while the second category is used to change its style and format. The third category won’t be tackled in this article, but it’s very helpful as it can add new information to the charts and even forecast values (not advised for short datasets though).
Back to our first line chart. With this new information, click the line chart to open its options in the Visualization pane. Now we can drag and drop the columns from our dataset into each Field of the chart. Let’s add the “Day” from the “Date” column to the Axis of our visualization, and both the “Download (Mb/s)” and “Upload (Mb/s)” to the Values. Oh, and do increase the size of the chart, as the default size is too small (use the resize markings on the border of the chart).
Ok, the line chart finally displays data but, judging by the values shown on the vertical axis, it doesn’t seem like it’s showing the average of the download and upload speeds on each day. Actually, by default for numeric values, Power BI displays the sum of the values. But we can easily change that with a right-click of the mouse on the Values of the line chart:
Change it for both the “Download (Mb/s)” and the “Upload (Mb/s)” and our first line chart is finished:
Now create a new line chart, to plot the “Ping (ms)” by “Day” again:
Notice how the data available on the Fields pane is ticked on or off depending on the visualization you have selected on the canvas. It’s a quick way of finding out which values are being used for each visualization.
With this, we’ve reached the end of the first part. We’ve created two line charts, one to visualize the evolution of the average download and upload speeds over the course of a month and another for the average ping.
We’ll return to these charts later to format them. For now, let’s create the gauges.
Create the gauges
Now we’ll create three gauges, one for each metric of the internet speeds. These will be useful to see where the average download, upload and ping, respectively, stand compared to the minimum and maximum values of each metric.
As before, create the gauge visualization for the “Download (Mb/s)” using the option in the top half of the Visualizations pane.
And again, like before, we’ll drag and drop the data into the Fields of the gauge. Since this first gauge is for the download speeds, we’ll use that column for the three Fields of the visualization: Value, Minimum Value and Maximum Value. The difference will be in how we display the data on each Field: just like we changed sum to average in the Values of the line charts, we’ll display the average, minimum and maximum values of “Download (Mb/s)”, respectively, on each Field.
And that’s it, this first gauge is ready. Now create two more identical gauges, one to display the “Upload (Mb/s)” and another for the “Ping (ms)”.
And that’s it. All the visualizations are now created, we just need to format them to make the dashboard more readable and its design consistent.
Format the visualizations
This last part of the article is somewhat optional. I’ve already showed you how to create the visualizations, now I’m going to show you how to make them look better.
I’ll show you three main points for this example and then you’re free to play around with the dashboard and the data as much as you want to: the title, the data colors and the axes. Just keep in mind, format these visualizations as much as you want to, the objective is for you to learn more about Power BI, the following are only my suggestions.
To keep the dashboard consistent, we need to use the same colors for each metric, that is, we should be using the same color for every instance of “Download (Mb/s)”, “Upload (Mb/s)” and “Ping (ms)”. Plus, we can also adjust the limits of the axes of the line charts slightly. For example, a month doesn’t have more than 31 days, so the upper limit of the X axis should be 31. Lastly, the title of the visualizations is the last thing we’ll change as the default title is too verbose.
For any format changes we want to make, we need to first select our target visualization and turn our attention to the second category in the bottom half of the Visualizations pane: Format.
In here, there are plenty of changes one can make to visualizations and I do encourage you to try them out and see what you like best for this dashboard.
My suggestions, in the case of the line charts, are to change the Start and End values of the X axis to 1 and 31, respectively, and disable the title of both the X and Y axes. In the Data colors options, choose different colors for each metric (three metrics, three colors). Lastly, in the Title options, change the text displayed and change the alignment to center. I think “Average Download and Upload (Mb/s) by Day of Month” and “Average Ping (ms) by Day of Month” are good titles.
If you were following along with the changes suggested in the previous paragraph, your line charts should look something like this now:
For the gauges, there’s not really much I want to suggest for format changes. Just change the Data colors to the ones used in the line charts as well as the titles.
Conclusions
And that’s it from me. Now go experiment with the Format options, try out different visualizations (tip: select one of the visualizations created and then in the Visualizations pane select a different visualization), try out different combinations of the metrics, do whatever your curiosity asks for to learn more about Power BI.
As promised, you can find a Python script to collect the internet speeds into a CSV file, as well as my version of the dashboard on GitHub here.
P.S.: the graphical interface of this application is in line with that of the other Microsoft Office products, like Word and Excel, so a lot of the functionality in Power BI is extremely intuitive :)
