This Power BI tutorial is intended to be hands-on. To follow along, download the Invoice Data from HERE and Customer Master Data from HERE. But first things first.
What is Power BI?
Before we tackle how to use Power BI, we should understand what it is. It’s a business intelligence (BI) tool from Microsoft. It allows you to import and model data from different sources, then analyze and visualize it, creating meaningful reports which you can securely share throughout your organization. It helps with informed decision-making, tracking key performance indicators (KPIs), identifying trends in the data.
It’s a must-know tool for any aspiring or seasoned data analyst.
Different Power BI apps
Power BI is actually a suite of products and services that each serve a different purpose. The main ones are:
- Power BI Desktop – a desktop application available as a free download; this is where you build the report.
- Power BI Service – an online portal for storing and sharing reports and dashboards. You can create a free account as long as you have a work or school email address, but the sharing capabilities only come with a paid license.
- Power BI (mobile) app – a free app for viewing the published reports and dashboards, available for Windows, Android and iOS.
You may also come across:
- Power BI Report Builder – a tool for authoring paginated reports (optimized for printing and PDF).
- Power BI Report Server – an on-premises alternative to Power BI Service. The published reports are stored on a local server and shared internally via a secure portal.
Power BI Desktop is the tool you’ll be using the most.
Installing Power BI Desktop
If you’re running Windows 10/11, Power BI Desktop can be installed for free from the Microsoft Store.
Just click the “Install” button and let Windows work its magic. The main advantage of this method is that it comes with automatic updates. Microsoft releases updates to Power BI monthly; this way you don’t need to reinstall every month.
If you are running an operating system other than Windows 10/11 (or a 32-bit version of Windows 10), you can download the Power BI standalone installer from the PowerBI.com website.
Click the “Download” link to download and install Power BI Desktop.
Featured Course
Fast Track to Power BI
Launching Power BI
Once Power BI has been downloaded and installed, launch Power BI and sign in with your work or school account.
NOTE: You can also work in Power BI Desktop without signing in, but you will have to sign in once you’re ready to publish the report to the Service.
The Power BI Main Components
Power BI Desktop is made up of three main components:
- The Report View
- The Data View
- The Model View
The Data View displays tables of data acquired from source(s).
The Model View displays the tables from the Data View in a bird’s-eye view showing the relationship connectors between the tables.
The Report View is where all visualizations are built and presented.
These visualizations can be tables, charts, graphs, slicers, KPIs, cards, buttons, logos, icons, etc.
Featured Course
Master Excel Power Query – Beginner to Pro
Importing and Transforming Data
The first step in any report is to acquire the data that will drive the report visualizations.
This data can come from a variety of sources:
- Delimited Text Files
- Excel Workbooks
- Databases
- Websites
- PDF Files
- Folders
- Online Services
The list of connectors is quite extensive and growing every month with regularly scheduled updates.
Clicking on Get Data provides a list of connectors.
For our demonstration, we will be pulling data from two different data sources:
- An Excel Workbook named “CustomerMaster.xlsx” that contains a table named “MasterCustomer”. This table contains information about our customers, like name, contact information, location, etc.
- A comma-delimited text file named “InvoiceData.txt” that holds invoice data (i.e., sales transactions.)
Loading the Invoice Data
Starting with the “InvoiceData.txt” file, we click Get Data then browse out to the text file.
We’re presented with a preview of the file’s content so we can verify that we have selected the correct file and that the proper delimiter is being used to parse the data.
We can click Load to load the data directly into Power BI’s Data Model, or you can click Transform Data to open Power Query and perform any needed transformations on the data in preparation for loading to the Data Model.
Even if you don’t think the data requires any transformations, it’s a good idea to load it into Power Query first just to ensure that the data is being interpreted correctly (e.g., dates are being read as dates and not text or numbers.)
Power Query can be used to remove unwanted columns, filter unwanted rows, and even enhance the data by creating calculations, such as calculating the Profit from the difference between Sale Price and Cost.
If you have experience using Power Query in Excel, those experiences are transferrable to Power BI. The program is 99% the same in both Excel and Power BI.
Applied Steps
Power Query performed several transformations steps automatically. These steps are listed on the right side of the window, as you can see in the above image.
- Source – the sales data was pulled from the source file.
- Promoted Headers – the first row in the data was promoted to a Header Row status.
- Changed Type – all columns were examined and assigned appropriate data types.
Although Power BI is good at performing certain transformations – like detecting data types – automatically, it’s a good idea to glance over the results to verify the actions are within your expectations.
Sometimes it’s not that an incorrect data type is assigned, but rather one “not as efficient”, e.g. a decimal data type assigned to a column of whole numbers. The decimal data type would work, but if there’s no need to store fractions, a whole number data type will utilize memory more efficiently.
Merging Columns
We could also perform operations like combining columns. In our text file, the “Year”, “Month”, and “Day” data is stored in separate columns.
We would like that information to be combined and transformed into a single “Date” column.
While holding down Ctrl key, select the columns “Month”, “Day”, and “Year” in that order, then click Transform -> Merge Columns.
We can define a Custom separator as a “/” (forward slash) and name the newly merged column “Date”.
The newly merged column appears as follows:
The data type is not set correctly; see the “ABC” icon in the upper left corner of the column?
We can set this column to a “Date” data type by clicking the “ABC” icon and selecting “Date”.
Notice that all the performed steps are listed in the Applied Steps panel on the right.
If someone updates the source text file, we will be able to click a button and refresh the data. All of the recorded steps will be executed on the new data to update our Data Model.
Loading the Customer Information
The customer information is located in the Excel file named “CustomerMaster.xlsx”.
Bring the new data in by selecting Home (tab) -> New Source -> Excel Workbook.
In the preview window, select the table named “MasterCustomer1” and click OK.
PRO TIP: If given the choice between selecting a sheet and selecting a table, it’s considered a best practice to select the table since it will confine the selection to the data range, and it will detect the header row if present.
Splitting Columns
A transformation we can apply to the customer data is to split the information in the “CityProvince” column into separate columns: one column for “City” and another column for “Province”.
We can see that there is a space & open parenthesis between the city and the province.
Select the “CityProvince” column then click Transform (tab) -> Split Column -> By Delimiter.
Set the delimiter to a space & open parenthesis ” (” and click OK.
We now have the cities and the provinces in separate columns.
One small issue is the leftover closed parenthesis at the end of the province names.
We will remove them by replacing all “)” characters with nothing.
Select the “CityProvince.2” column and click Transform -> Replace Values.
In the Replace Values dialog box, enter a closing parenthesis in the “Value to Find” field and leave the “Replace With” field empty.
Now with perfectly separated cities and provinces, rename the columns “City” and “Province” respectively (simply double-click on the column header to rename).
Remove unnecessary columns
Lastly, we’ll remove the unneeded columns from the table by selecting Home -> Choose Columns.
Uncheck the unwanted columns and click OK.
Removing columns that do not contribute to the visualizations or aid in underlying functionality is a great way to reduce the size and complexity of the Data Model while simultaneously improving performance.
As with the invoice data, all the transformation steps for the customer data have been recorded and will execute automatically when the data is refreshed.
Final steps
Rename the query “MasterCustomer” using Properties – Name on the Query Settings pane.
We are ready to load the results of the Power Query transformations into the Data Model.
Click the button labeled “Close & Apply” on the Home ribbon.
After the data is loaded into the Data Model, switch to the Data View and examine the results of the query output.
Expanding the two titles on the right in the Data (previously Fields) pane shows all the loaded columns for each table.
Editing the Data Transformations
If you need to return to the Power Query Editor to modify the original transformations, right-click the query name on the Data (previously Fields) pane and select Edit Query.
Mastering Power Query is the first step in learning how to use Power BI to the fullest.
Featured Course
Power Excel Bundle
Exploring the Data Model
Switching to the Model View, we can see that the two tables have been connected using a relationship.
The relationship has been built between the “InvoiceData – CustomerCode” column and the “MasterCustomer – CustomerID” column.
The table relationship allows us to perform real-time lookups of information from one table to another without having to merge the two tables into a single, giant table.
The “InvoiceData” table serves as the fact table (the table of things that happened), while the “MasterCustomer” table serves as a lookup table to enhance the facts.
This would allow us to take a [CustomerCode] and discover the [City] that the customer belongs to.
Creating Relationships Automatically
Power BI has a setting that permits automatic creation of relationships. This setting can be activated or deactivated by selecting File -> Options & Settings-> Options -> Data Load -> Relationships.
If the relationships are not created automatically (this can happen when working with unstructured sources with dissimilar field names), you can easily create them by dragging a field from one table and dropping it atop another field in a different table.
Building a Report
Reports are created in the Report View.
Creating visuals in Power BI is simple. Check the boxes to the left of the fields of interest and let Power BI do the rest.
Power BI has determined that the best chart for this data set is a Column Chart. This can be changed to any other chart in the library by clicking the desired chart icon in the Visualizations Library.
Filtering the Data
We’ve switched the original Column Chart for a Bar Chart.
This chart is examining sales for all years. Suppose we need to report sales by specific years.
Just like in Excel, we can add a Slicer visualization to easily filter between various years.
BEWARE: If you click an icon in the Visualizations Library while an existing visual is selected, you will change the existing visual. To create a new visual, ensure no existing visual is selected on the report.
To add Slicer visual to the report, click the Slicer icon in the Visualizations Library.
With the Slicer placeholder selected, click the [Year] field from the InvoiceData – Date – Date Hierarchy.
Power BI automatically created the Date Hierarchy to aid in the reporting of data by common periods (e.g., quarters, or months.)
NOTE: If you are not getting the automatic Date Hierarchy when working with tables that contain date-based information, the “Auto Date/Time” may be disabled. To enable the feature, click File -> Options & Settings -> Options -> Data Load -> Time Intelligence -> Auto Date/Time.
If you aren’t a fan of the Timeline version of the Slicer, you can change the Slicer Style on the Format pane. Select the slicer visual and go to Format -> Slicer settings -> Options -> Style.
NOTE: The slicer type setting has moved since the video was recorded. You will now find all the settings in the Format Options.
Setting the Slicer to a Vertical List style yields the following:
Selecting a Dropdown style yields the following:
And Tile style yields clickable buttons.
Formatting the Visuals
To format almost any visual aspect of the visual (e.g., font size, border, shadow, colors, labels, etc.) click the Paintbrush icon on the Visualizations pane.
NOTE: Microsoft has redesigned the interface of Power BI Desktop since the video was recorded, with many esthetic changes affecting the Format visual interface. The version of Power BI you are using likely differs from what you see in the video. The basic behaviors should be comparable, though.
The available options will differ based on the type of visual you’re formatting. Expanding a feature will reveal the customization controls for that feature. The On/Off toggles turn features completely on or off.
In our project, we will turn off the titles for both the Y and X axis and change the color of the bars to orange.
We also changed the title of our Bar Chart to “Sales by Category” to make the title more meaningful. You will find the Title under General formatting properties (i.e., not visual-specific, common to all visual types).
Tooltips
Power BI is equipped with popup Tooltips. These appear when you hover the mouse pointer over a visual’s data element.
Tooltips provide detailed information such as category name and aggregation value. They can be highly customized to even display mini charts which are filtered by the datapoint over which you hover.
Building a Table of Calculations
Now we’ll build a table that calculates the following:
- Sales by Month
- Year-Over-Year Percent Change
- Sales Year-to-Date
To do this, we will utilize Power BI’s Time Intelligence capabilities without writing a single formula.
Add a Table visual to the report.
Place a check next to the [Month] field in the Date Hierarchy and the [Sales] field of the “InvoiceData” table. Notice the Sales values have been summarized automatically, like they would be in an Excel pivot table.
Comparing Sales of Same Month to Previous Year
If we are slicing (i.e., filtering) for the year 2019, to display the percentage change in sales for each month against the previous year, we can create this calculation using the Quick Measure feature located on the Home ribbon.
Populate the Quick Measures dialog box as follows:
- Calculation = Year-over-year change
- Base Value = Sum of Sales
- Date = Date
Calculating Sales Year-to-Date
To display the total sales year to date, we can create this calculation using the same Quick Measure feature located on the Home ribbon.
Populate the Quick Measures dialog box as follows:
- Calculation = Year-to-date total
- Base Value = Sum of Sales
- Date = Date
Data Analysis Expressions (DAX)
What you’ve just done, without realizing, is written DAX measures. DAX, or Data Analysis Expressions, is the formula language of Power BI (as well as Excel’s Power Pivot). It underpins every calculation used in data visualizations, even if it happens “under the hood”, whenever you add a value field to a visual.
Or when you use Quick measures, which are ready-made formula templates that you just plug your data to.
Featured Course
Master Excel Power Pivot & DAX (Beginner to Pro)
Adding More Visuals and Exploring Interactions
Visualizing Sales by Province
Our next visual will be a Filled Map chart.
Select [Sales] from the “InvoiceData” table and [Province] from the “MasterCustomer” table.
(Most) Everything is a Filter
One of the great things about Power BI visuals is that almost every visual serves double-duty as a filter. Slicers are not the only way to filter data in Power BI.
If you were to click on the bar for “Supermarket” in the Bar chart, all of the calculations in the table will change to reflect “Supermarket” values. The Filled Map chart will also change to show only states where “Supermarket” sales occurred. This is called cross-filtering.
To remove a filter, either click the same item you clicked to invoke the filter (this acts as a toggle) or click any empty area of that visual.
Displaying Quantity by Month in a Line Chart
Lastly, we will create a Line Chart visualization that displays the quantity sold by month.
Place a check next to the [Month] field in the Date Hierarchy and the [Quantity] field of the “InvoiceData” table.
Finishing Touches
Adding a Report Title
We can add a header to the report using the Text Box feature located on the Home ribbon.
Adding KPIs (Key Performance Indicators)
A KPI allows us to track a specific single value that is of high importance.
If we want to track the total quantity sold, we can add the Card visual and set its analysis field to [Quantity].
Rename the Report
Double-click the page tab on the bottom and rename it from “Page 1” to “Report”.
Save the file with a name of your choosing.
Publishing Your Report
If you need to share your report with others in a way that provides them full interactivity, you’ll need to publish the report.
An advantage to publishing is that you only need to send the viewers a link to the report, not the Power BI file itself.
This link will display the report in the viewer’s web browser.
Another advantage is that when you make changes to the report or update the report data, you don’t need to send out a new file or link to the viewers. Just update the published report and they will see the latest version.
To publish the report, click Publish in the upper-right of the Home ribbon.
Select your desired workspace created in your Power BI Service account (with a free account only “My workspace” is available).
When the upload completed, you will see a notification that the report has been successfully published to Power BI Service.
You can click the supplied link to immediately view the published report in a web browser. This allows you to see what the viewers will see and check for any irregularities.
Sharing the Report with Others
To share the report via a web link, click on the Share button at the top of the window.
From the sharing dialog box, either enter the email address of the person you wish to share the report with or copy the link to be used in an email or instant message app.
You can also start a new email message using Outlook or share the report via Microsoft Teams.
You can even embed an interactive report in a PowerPoint presentation.
Explore more amazing features of Power BI with our tutorials, or check out this FREE Power BI Quick Start Course.
Practice Files
Download the Invoice Data HERE.
And Customer Master Data HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
