Today we’ll match up the data visualization power in Power BI to the ARR in R.
Every time I see one of these post about data visualization in R, I get this itch to test the limits of Power BI.
Tonight I read a post about Plotting time series in R using Yahoo Finance data by Joseph Rickert on the Revolution Analytics blog.
In his blog he describes, in its most simple form, how he gets stock data from the Yahoo Finance API and plots it on a chart.
Sounds like something Power BI can do!
He then goes on to describe making the chart interactive using fancy R magic.
Let’s break down what we’re seeing here.
- Data for IBM and LNKD stock
- Mouseover legend
- Date range selector
- Interactive HTML presentation
The date range selector will be a hard one, but let’s get this party started!
Importing the data
With anything Power BI, the first step is getting the data.
In his blog, Joseph posts the following links as his sources
Opening these in a browser, downloads a csv file. However, we want to be able to open the data in the fancy R way with the variables in the URL. This also enables us to easily adjust the time period in the future.
Importing this data in Power BI is done via the “Web” data source
In the next, overly large dialog box, with a very small input box we enter our first URL
Pressing “OK” gives us the following dialog box.
The “Edit” button will give us the Power Query screen which gives us a lot of options to edit our existing data or even create new features in our dataset. To compete with the abilities of a specialized tool like R we’ll need to use the full potential of Power BI.
So press that “Edit” button and then import the next dataset as well using the same method.
You should end up having 2 datasets loaded, seeing the below screen.
Cleaning the data
As you might have noticed, the values were imported as if the stock was worth millions to billions. I’m sure IBM and LinkedIn will be happy to hear this. However, you might have noticed that stock is usually worth several dollars for small companies to only hundreds of dollars for the top companies.
I’m thinking that the data might appear correctly for you, depending on your locale. The period vs comma decimal dilemma is something typical for Belgium.
To work around this problem in our data import we’ll take the following steps
- Convert the numerical columns to text
- Replace the period by commas
- Convert the decimal columns to a decimal data type
- Convert the whole number column (Volume) to a whole number datatype
Et voila! We’re done cleaning our data.
Joining the data
To show both datasets in one chart we need to join the data first.
But to identify the joined data in one chart, we’ll need to add a column in both datasets before we join them.
Adding a column is done via the aptly called menu item “Add Column”.
There’s a plethora of choices here but we’ll just go with “Add Custom Column”
For each dataset, add a column called “Company” and mark it as having data for the specific query using a formula as seen in this screenshot.
Next we’ll need to combine the 2 datasets.
This can be done from the home menu using the “Append Queries” item.
After pressing the “Append Queries” item, you’ll get the following dialog box
It conveniently shows you which dataset you’ve currently got selected. That way you can wisely pick the dataset that you want to append to the current one.
Next you can click the “Close & Apply” button
Creating the report
Click the “Report” tab and let’s get to work!
4 steps will get you the basic setup
- Click the 3 fields you want
- Click the line chart visualization
You’ll see this beautiful chart that you’ll recognize from the original article.
We got our data, we got a line chart and we got our mouse over information. Now we need to create our date range selector.
There are several ways to implement this.
Sadly, the cool slider thingamajig that R has isn’t one of them.
So what can we do to create a cool date range selector in Power BI?
Power BI enables us to use slicers or even use a data visualization, like a treemap, as a sort of slicer.
Adding features to our data
We’ll probably want to slice on year, quarter and month. Let’s add the relevant data to our dataset.
For this, we need to edit our dataset, so let’s press the “Edit Query” button again.
Adding these fields is a breeze, we select a date field and go to the Add Column menu.
From there we see one of the most incredible options. We can add different time columns with the click of a button.
Adding custom features to our dataset
Adding custom features to your dataset can be done using DAX.
DAX can be the subject for a lot of other blog posts but for an easy start check out Dustin Ryan’s blog post on some often used DAX formulas.
For our little experiment we don’t need to go this far however. So feel free to explore or give your suggestions in the comments or via twitter.
In Power BI you can filter a view, that’s one page in a report. There are several possibilities to create filters, a slicer is one of them. A slicer is a filter that is present on the report page itself. Like the one in the original R visualization. Filtering works with data that belongs to the same query or with queries that have a relationship defined between them.
One of the slicers we’ll create is by using a treemap.
Create a treemap visualization and put the quarter name in there like in the screenshot.
This gives us a quite ugly slicer with different colors. We can change these colors via the format menu.
Changing these colors gives a great result that will fit with the overall look and feel of our dashboard. It will especially help us avoid making this thing look like a coloring book.
If you’ve been following the process yourself, you probably noticed how fast and easy it is to toy around in Power BI.
Leave a comment if you have a question or encountered a problem. Below is the result I got.
If you haven’t played around in Power BI yet, just download the free Power BI Desktop application today and get started with the Power BI Zero to Hero series.
It’s a lot more user friendly than writing some lines of R magic. I’d even argue that it’s more fun!
Follow me on twitter to see when I create another wine fueled Power BI post.