Power BI is an analysis tool created by Microsoft. It is part of a class of software tools known as self-service business intelligence tools, which means that it is supposed to be easier than most analytics-based tools. However, it still has a learning curve that users need to master. This tutorial will help you get your start in learning the tool.
Power BI is an analytic tool that allows users to build sophisticated reports. These reports go above-and-beyond the capabilities available in Microsoft Excel. In fact, Microsoft tried to retrofit Excel to provide the type of features available in Power BI, but the company felt it was easier to start from scratch.
If you are familiar with Pivot tables in Microsoft Excel, then you have a glimpse of what Power BI can do. However, pivot table functionality only scratches the surface of the power that is generated by Power BI. Experienced Power BI users would readily agree that the software is much more than an enhanced pivot table. If they didn’t agree, their expertise would be seriously called into question.
Power BI is not difficult per se, but it has subtleties that trip up even experienced users. It takes practice to master the program, but it won’t take years like many analysis software packages.
Start with Data
I’ve experienced several tutorials in Power BI. Many of them were quite good. However, in almost all cases, the instructors present full-scale data models, which can leave novices feeling overwhelmed about how the tool ties all the pieces together.
I am taking more of a bottom-up approach by working with a single table with only a few records. This will help you see the relationships easier. It’s not a method you should ever use to structure your data for your actual projects. However, as we progress with the lessons, we’ll build out the data model in the proper manner.
This approach will help you in a few ways. First, it will let you see how the tool works without getting bogged down initially with the relational aspect of multiple tables. We’ll start with a single table that has all the needed data for the first set of tutorials.
Second, if you work with clients who have data sitting in Excel sheets, chances are these sheets will contain most of their data (and implied relationships) within one spreadsheet. They’ll record a transaction, including the products sold for that transaction and the full details of the customer all on one row of a spreadsheet. This will repeat for each transaction.
From a client’s perspective, this helps them easily see all the data, and they can reference customers or products by performing a simple Excel search. Because the data is in this format, which is how we’ll start our analysis, you’ll be an old pro at transforming the data when you go through these tutorials. You’ll be capable of taking these transaction = Excel row spreadsheets and get them into a proper format where Power BI will perform at its best.
Scope of Part 1 of the Project
To try and teach an entire Power BI course in one tutorial would not do it justice. There’s simply too much involved. Therefore, I’ll break it down into several tutorials. This will also serve as a good reference when you need to reinforce concepts.
Wait! No Video?
I plan on creating a video tutorial for this article. As much as I love learning by video, though, it's nice to have a reference tutorial via text (and visuals) that you can scan when you need a quick answer. With videos, you have to find the place in the video when you are trying to refresh a concept that you learned.
The scope of part 1 is to learn about a sales table data structure that contains only a few Excel rows. For this beginning tutorial, the columns in this table will contain:
- Order ID
- Transaction Date
- Customer ID
- Customer Name
- Product ID
- Product Name
- Amount (Quantity x Price)
It’s likely small business owners would not associate a customer ID with their customers or product ID with their products. However, it’s quite easy to create a sequence of numbers which is all you need for a customer ID (or product ID). This will be useful later when we separate the customer and product information into their own tables respectively.
The customers are fictitious. The products are real products, but don’t represent actual prices. The Transaction Date contains transactions for the previous year and the current year (at the time of this writing). This will come in handy when we explore date concepts later.
The data is contained on my GitHub page. Download the data to following along.
Start Power BI Desktop
NOTE: this tutorial assumes you already have Power BI Desktop installed. If not, please click on this link to install it. It's free and easy to install. You can also click on the image:
When Power BI first starts, you’ll see a splash screen. You have the option of loading the data (1), or close out the splash (2), which then you can load the data inside the program. Which you choose is a personal preference.
For this tutorial, we’ll close out the splash (2). Then, we’ll open the file from inside the program.
Related: Why you should learn Power BI
After the Splash page is closed, on the Home page, follow the sequence of steps:
- Select the Get Data button.
- Make sure the Excel option is selected. We’ll cover the other options in more detail later. Since our data is contained in an Excel spreadsheet, that’s the option we’ll use for this tutorial.
- Click on Connect.