Installing Power BI

To follow along: download the Invoice Data from HERE and Customer Master Data from HERE.

If you’re running Windows 10/11, Power BI Desktop can be installed for free from the Microsoft Store.

Just click the “Get” button and let Windows work its magic.

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 website.

Click the “Download” link to download and install Power BI Desktop.

Featured Course

Fast Track to Power BI

Stay ahead of the game in 2023. This course gets you started in Power BI – Fast!
Learn More
Power BI Course image

Launching Power BI

Once Power BI has been downloaded and installed, launch Power BI and sign in with your work or school account.

The Power BI Main Components

Power BI is made up of three main components:

  • The Report View
  • The Data View
  • The Model View

The Data View displays tables of acquired data from sources like those previously stated.

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 presented.

These visualizations can be tables, charts, graphs, slicers, KPIs, bookmarks, etc.

Featured Course

Master Excel Power Query – Beginner to Pro

Power Query is an Essential tool for Data Analysis. Learn to Collect and Analyze Data with Ease.
Learn More
Power Query Course cover

Acquiring 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 Spreadsheets
  • 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 sources:

  • An Excel Workbook named “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 “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 (ex: 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.9% the same in both Excel and Power BI.

In the above image, several transformation steps were performed.  These steps can be seen listed on the right side of the window.

  1. SOURCE – The sales data was pulled from the source file.
  2. PROMOTED HEADERS – The first row in the data was promoted to a Header Row status.
  3. CHANGED TYPE – All columns were examined and assigned appropriate data types.

Although Power BI is good at performing certain transformations automatically, like detecting data types, 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, rather a “not as efficient” data type is assigned.  Such as a fractional data type is assigned to a column of whole numbers.  The fractional data type would work, but if there’s no need to store fractions, a whole number data type will utilize memory more efficiently.

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.

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 -> New Source -> Excel Workbook.

In the preview windows, 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.

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 parentheses” between the city and the province.

Select the “CityProvince” column then click Transform -> Split Column – By Delimiter.

Set the delimiter to a “space & open parentheses” and click OK.

We now have the cities and the provinces in separate columns.

One small issue is the leftover closed parentheses 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.

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.

Rename the Query “MasterCustomer”.

We are ready to load the results of the Power Query queries 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 Fields panel 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, select the Data View, then right-click the query name and select Edit Query.

Featured Course

Power Excel Bundle

Includes Power Query course, Power Pivot and DAX (Learn in Excel, Apply in Power BI as well)
Learn More
Power Excel Bundle course cover

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.

Setting Up 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 (ex: 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 click the down-arrowhead (upper-left of Slicer) and change the slicer to other interface formats.

Setting the Slicer to a List interface yields the following:

Selecting a Dropdown interface yields the following:

Featured Course

Master Excel Power Pivot & DAX (Beginner to Pro)

It’s one thing to get data into Excel. It’s another to turn that data into meaningful reports.
Learn More
Power Pivot DAX course cover

Formatting the Visuals

To alter almost any visual aspect of the visual (ex: font size, border, shadow, colors, labels, etc.) click the Paint Roller icon located below the icon library for visuals.

NOTE:  As of this writing (Dec. 2021) Microsoft is currently in a redesign phase for the Format Visual interface.  The beta format appears below (right).  The version of Power BI you may be using may look like this or appear similarly.

The On/Off toggles turn features completely on or off.

Expanding a feature will reveal the customization controls for that feature.

We can change the title of our Bar Chart to “Sales by Category” to make the title more meaningful.

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.

Tooltips can be customized to even display mini charts.

Turn off the titles for both the Y and X axis.

Change the color of the bars to orange.

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 features 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.

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

Visualizing Sales by Province

Our next visual will utilize a Filled Map visual.

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.

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 the 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 sheet tab 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 all is well.

It’s no different than when Google updates their daily doodle (the Google logo based on an important event of the day.)  You don’t get an email telling you that it has been updated, you just see the latest doodle when you visit the site.

To publish the report, click Publish in the upper-right of the Home ribbon.

Select your desired workspace created in your Power BI account.

When the upload completes, you will see a notification that the report has been successfully published to your Power BI workspace.

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 that may have occurred during the design or publishing phase.

This report has all the same interactivity and filter capabilities as the local version on your PC.

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 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.

Practice Workbook

Download the Invoice Data HERE.

And Customer Master Data HERE.

Leila Gharani

I'm a 5x 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.