Power BI

Introduction

Have you ever heard of Power BI? Produced free of charge by Microsoft, it’s a sort of a link between Power Query and Power Pivot and Business Intelligence. Whilst Power BI is a stand alone product, it is integrated with Excel and other software too.

For example, you might have gathered that my wifi connection swings from being to to being dire. Every now and again I check the speed of my wifi connection to see if I should bother downloading or trying to upload something … diagnosis software saves the results in CSV format and I uploaded them to my laptop. The data look like this:

The Data

speedtest_1

In fact, I checked these speeds using two devices so there is a similar but different file from the other device too.

So? Now what? Excel can easily handle such files, can’t it! Yes it can and I analysed the data using Excel at first. I opened the files in Excel as CSV, copied and pasted them into the same worksheet, converted the range to a table … Ctrl+T and saved the file as an Excel file.

Simple!

Using Power BI

I also used Power BI, like this. Opening Power BI can start with this splash screen, although you can turn it off!

Get Power Bi from www.powerbi.com … free of charge! There are lots of examples and tutorials on the site too.

speedtest_2

Notice, I am using Power Bi for Desktop and there are other versions!

Power BI Output

Firstly, this is what I did in terms of data analysis:

All data: download speeds, upload speeds and latency or Ping

speedtest_3

Maps of all data: notice how the speed test software included latitude and longitude: you can see where I checked the speed of my connection and by size of bubble how strong it was.

speedtest_4

Averages, maxima and minima for my data alone: I chose column charts fro everything but there are many types of graph to choose from.

speedtest_5

The controls that help you to manage all of this comes in two parts, a ribbon and a visualisation and task pane, like this:

speedtest_6

speedtest_7

Let’s import the data first of all by opening a new file

File … New … Power BI opens a new instance with the splash screen if you tick show this page on startup … I always do

On the splash screen click Get Data on the left hand side

Select the data type from the dialogue box that opens:

speedtest_8

Click Connect

Locate and select your file … I chose CSV in this case

speedtest_9

Click Edit so we can make corrections and changes if we have to:

If you have used Power Query already you will now see something very familiar … in essence the sam

Check that column headings are corret

Check for Data Type

Look for any obvious errors such as Null, Error, text where there should be numbers … anything odd

Click a down arrow at the top of a column to deselect anything that you think looks wrong

Delete a column if you don’t need it … you decide that

When you are happy, click Close and Load and your data will be imported as a table. It’s now ready for your analysis.

In this example, I was given this:

speedtest12

Which is a table called speedtest_310316_b and you can see the column headings of ConnType, Date and so on on the right hand side

If you want to look at the table again, inside Power BI, click on this icon on the left hand side of the screen:

speedtest13

And you will see the full version of this extract …

speedtest14

Click on the top of those three icons now to go back to the report area which is blank at this stage:

Let’s create a column chart of download speeds by date

Click date

Click Download …

That gives you a table so now click Stacked Column Chart in the top row of the visualisation pane and you should get this:

speedtest15

The fonts are a little small but let’s concentrate on creating visualisations for now.

With the column chart selected click Ctrl+C to copy it then click Ctrl+V to paste it. The two column charts will overlap so drag your new chart and place it next to the first chart …

speedtest16

Try and align you two charts. Now, select the second chart and click on   the tablee icon in the visualisations pane … then click on the Treemap icon … then click on the Line chart icon …

You get the idea! With almost no effort by copying and pasting, you can create a report or dashboard

 

speedtest17

  • A Stacked column Chart
  • A Line Chart
  • A Treemap of Latency
  • A table of averages by Connection Type

I edited the titles as well as the font size in the table by clicing on the paintbrush icone in the visualisation pane and making the appropriate selections … which are easy to use!

Conclusion

There you are: a whistlestop introduction to Power BI using a CSV file and analyse the results of the speedtest of my wifi connection. See my comment on downloading my files below.

Try it!

 

Duncan Williamson

 

My apologies but I cannot upload the files here but please write to me, by email or comment here and I will send them to you.

 

 

%d bloggers like this: