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:
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.
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.
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
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.
Averages, maxima and minima for my data alone: I chose column charts fro everything but there are many types of graph to choose from.
The controls that help you to manage all of this comes in two parts, a ribbon and a visualisation and task pane, like this:
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:
Locate and select your file … I chose CSV in this case
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:
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:
And you will see the full version of this extract …
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 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:
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 …
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
- 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!
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.
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.