Baby Steps: How To Build Basic Charts in Power BI Desktop

By: Matt Wollner

Sr. Business Intelligence Consultant at Key2 Consulting

 
 

I’ve spent a lot of time talking to business owners and managers. And I’ve noticed that many of these leaders tend to use excel for their analytics needs. Furthermore, they are interested in using free analytics software like Microsoft Power BI Desktop to improve their organization’s business intelligence and decision-making. Yet, they’re not doing so.

Why not? The most common reason I’ve heard:

“starting out with the technology is very overwhelming.”

That makes sense to me. Power BI Desktop has myriad features right out of the box…so many that starting out can certainly be daunting.

So if you can relate to that paralysis, great – you’re in the right place. Taking the knowledge from this post will be your first steps towards getting the most out of the tool.

After all, Rome wasn’t built in a day right?

Let’s begin.

Microsoft Power BI Desktop is a useful analytics tool that is currently available as a free download. It’s an elegant end-to-end solution for building analytics with state-of-the-art interactive visualizations. Out of the box, it’s very simple to import data from many data sources: SQL, Excel, CSV, JSON, or a grid on a web page.

1. Download the latest Microsoft Power BI Desktop here if you haven’t yet.

2. When you open Power BI Desktop, you are prompted to Get Data. Under Other, select Web.

3. Some of the easiest web data to pull into Power BI Desktop comes from an online grid or table. For this example, I am going to pull Atlanta’s historical weather data from: http://bit.ly/2nagwh0

4. You will now be prompted to select the data from the web page that you are interested in. For this web page, Power BI Desktop has discovered three data sections: Document, Table 0, and Table 1. You can preview the data by clicking on each of these elements.

We can see that Table 0 is the table that matched the web table.

5. The data is now loaded into Power BI Desktop.

On the right-hand side, you will see all the data elements that were imported. Power BI Desktop has determined which columns are numeric and which can be aggregated. Notice that some of the fields, like Total Snowfall (In), are missing the Σ.

Now let’s look at the data that was imported and do some very basic data cleanup.

 

1. Click on the Data icon on the left hand side in order to view that data in Power BI desktop.

2. If you click on Total Snowfall (In), you will see under the Modeling tab that the data type is listed as Text. If you look at the data in the column, you will see some values that are set to NR.

This is causing the wizard to set this column to Text instead of Decimal. We need to remove the NR and convert the column to a decimal. One way to accomplish this is to create a new Column.

3. Under the Modeling tab, select New Column.

You may notice there’s an option to add a column or a measure. A Column will store the data at the row level, while a Measure will calculate the value on the fly in the report based on the available values.

4. Enter the following DAX query to replace all NR values with BLANK.

For more info on DAX:

https://msdn.microsoft.com/en-us/library/ee634396.aspx

5. Under the Modeling menu, set the Data Type to Decimal Number.

We now have a new Column – Total Snow – which only has decimal values. While I’m on the Data tab, I’m going to rename my columns to make them shorter. I’m going to do this by double clicking on the column header and editing the name.

Let’s switch back to the Reporting page. Click the Report icon at the top left of the page to start creating a report. You will notice Total Snow is now in the Fields list on the right-hand side.

The Visualizations available and a current field list are displayed on the right-hand side of the Reporting page. Each Visualization has its own attributes that can be populated by your given fields. The last Visualization, Import from File, allows you to develop your own visualizations or download one of several currently available free visualizations, found here:

https://app.powerbi.com/visuals/

Now let’s create a basic line chart.

1. Select the line chart from the Visualizations.

2. Drag the following Fields to the Visualization attributes.

a. Year – Axis

b. Highest Temperature – Values

c. Lowest Temperature – Values

d. Average Maximum Temperature – Tool Tip

e. Average Minimum Temperature – Tool Tip

3. If you click on the down arrow next to any of the attributes in the chart, you can easily change the value from a sum to another aggregate function.

4. If you scroll down to the Filters section you can quickly filter the data that is shown in the chart.

If you add multiple charts to the report, they will automatically work together. I am going to add a Slicer to slice by decade.

 

1. Resize your existing report so that there is space to add a new Visualization. Drag in a Slicer.

2. Drag Year to the Field.

3. Click the down arrow next to Year and select New Group.

4. A Group lets you group data together. The Group Type List option will let you drag and drop values into a Group. The Group Type Bin splits data numerically.

a. Group Type: Bin

b. Bin Size: 10

5. Now the Decade you select in the Slice will automatically filter the line chart.

 

So there you have it – a quick overview of how to create some basic charts in Microsoft Power BI Desktop. Power BI Desktop is an amazing tool and can work wonders for companies of all industries when utilized properly. If you have any questions about Power BI or anything analytics-related, contact us anytime and we’d be happy to help.

Also, don’t forget to subscribe! Thanks for reading.