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.

Get Data, Microsoft Power BI Desktop

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

Georgia weather data to be used for Microsoft Power BI report

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.

Microsoft Power BI Desktop navigator

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.

Microsoft Power BI Desktop data icon

Microsoft Power BI Desktop data table

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.

Microsoft Power BI Desktop modeling

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.

Microsoft Power BI Desktop, setting data type

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/

Microsoft Power BI Desktop data visualizations

Now let’s create a basic line chart.

1. Select the line chart from the Visualizations.

Microsoft Power BI Desktop line chart 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

Microsoft Power BI Desktop line graph, weather for Atlanta

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.

Microsoft Power BI Desktop aggregate function

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

Microsoft Power BI Desktop filters

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.

Microsoft Power BI Desktop slicer

2. Drag Year to the Field.

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

Microsoft Power BI Desktop 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

Microsoft Power BI Desktop groups

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

Microsoft Power BI Desktop 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.

Share
Tweet
Share
Reddit
Email