POWER BI MADE EASY – PART 1: Get The Data

WHAT IS power bi?

Today, data is the competitive advantage of business. However, we now generate mountains of data that is exceedingly difficult and time consuming to analyze. To quickly make informed decisions, businesses look to data visualization to extract key insights. One of the most common tools used in corporations today for data visualization is Power BI.

Power BI helps companies pull data together, transform, summarize and create visualizations to tell compelling stories. Power BI has a desktop version called ‘Power BI Desktop’ and a cloud based version, called ‘Power BI Service.’ The desktop tool is used used to connect, clean and create visualizations while the cloud based version is used to collaborate and share visualizations in dashboards.

So where do Business Intelligence tools like Power BI, and competitors such as Tableau and Qlik,  come into play within the greater analytics process? As shown in the below visual, Business Intelligence bridges the gap between opportunity hypothesis and turning insights into action.


WHat will we cover in this series?

Across this series, there are 5 sections we will cover:

  1. Get the data
  2. Join tables and model the data
  3. Visualize the data
  4. Analyse the data
  5. Share the vizualizations on Power BI service

Download POWer BI Desktop

Before we start, go ahead and download Power BI Desktop at the below URL. Please note that you need a PC for Microsoft Power BI.

https://powerbi.microsoft.com/en-us/desktop/

Part 1 - Get the data

In this first part, we will go through:

  • Quick tour of Power BI
  • Get data into Power BI
  • Profile the data
  • Clean transform and load the data

1.1 Quick Tour of Power BI

When you first launch Power BI, you will notice there are 3 icons on the left side of the screen.

The first is the ‘Report View’. Reports are collections of visualizations that appear on one or more pages. This is where you collate visualizations to help tell your story.

The second is the ‘Data View’. This enables you to see the underlying data tables which the reports are based off.

Lastly, the ‘Model View’ is where you create relationships between your tables. If you are new to databases, relationships are how you connect tables together through common keys. 

When you first get started, there are 2 important buttons on the Home ribbon. The first is the ‘Get data’ button. This is where we connect Power BI to the data source, which could be anything from Excel to a Cloud based Azure SQL database. The second important button is ‘Transform Data’. This button launches Power Query, the tool used to clean and transform your data. You can think of Power Query as a visual tool that writes code (using the M language) to prepare your data.

1.2 Get the data

A common data type you will load into Power BI is a flat file. This may be a CSV or an Excel file. Let’s go through the process of loading an Excel file with some financial data from a company called ‘Office King’, who sell office furniture globally.

Go ahead and download the file ‘Financials_OfficeKing’ below:

In the Home tab, click on Get Data -> Excel.

Navigate to the location you downloaded the file ‘Financials_OfficeKing’ and select OK. You will then see a navigator screen. First, select the Excel sheet ‘factFinancials_OfficeKing1’, then select ‘Transform Data’. It is a good habit to transform the data immediately, rather than loading the data and making transformations later. When you click on ‘Transform’, you are taken to Power Query, where you will be able to do some quick data cleaning.

Now that we are in Power Query, the first thing we will do is rename the table to something more user friendly. In the right corner, let’s rename the table to ‘Sales’.

One of the most common issues when loading data, is Power BI may get column data types wrong. If we look at all the Price columns, we will see that these should be labelled a currency, not a decimal. Let’s start with the column ‘Manufacturing Price.’ 

If you right the icon in the left corner of the column with a ‘123’ symbol, you will get a dropdown of alternative data types. Click on the second selection that starts with a ‘$’ sign and says ‘Fixed decimal number.’ You will see a dialog pop up, you can select ‘Replace current step’. 

Note that for every change you make in Power Query, there is an applied step shown on the right panel under ‘Applied Steps.’ If you add a new step and want to undo the step, simply delete the applied step and you will return to your previous state.

We can change the data type for the next column ‘Sale Price’ and ‘Gross Sales’ and so on, however there is a faster way to convert multiple column types at once.