Now that you know the basics of Microsoft Power BI, let’s jump into some hands-on experiences and a guided tour.
As you learn about all the things that can be done with Power BI, keep in mind that all these activities, and all the analysis that’s done with Power BI, generally follow a common flow. The common flow of activity in Power BI looks like this:
Bring data into Power BI Desktop, and create a report
Publish to the Power BI service, where you can create new visualizations or build dashboards.
Share dashboards with others, especially people who are on the go.
View and interact with shared dashboards and reports in Power BI Mobile apps.
As mentioned earlier, you might spend all your time in the Power BI service, viewing visuals and reports that have been created by others. And that’s just fine. Someone else on your team might spend all her time in Power BI Desktop, which is fine too. To help you understand the full continuum of Power BI and what it can do, we’ll show you all of it. Then you can decide how to use it to your best advantage.
So, let’s jump in and step through the experience. The first order of business is to learn the basic building blocks of Power BI, which will provide a solid basis for learning how Power BI turns data into cool reports and visuals.
Microsoft Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Whether your data is a simple Microsoft Excel workbook, or a collection of cloud-based and on-premises hybrid data warehouses, Power BI lets you easily connect to your data sources, visualize (or discover) what’s important, and share that with anyone or everyone you want.
Power BI can be simple and fast, capable of creating quick insights from an Excel workbook or a local database. But Power BI is also robust and enterprise-grade, ready not only for extensive modeling and real-time analytics, but also for custom development. Therefore, it can be your personal report and visualization tool, but can also serve as the analytics and decision engine behind group projects, divisions, or entire corporations.
If you’re a beginner with Power BI, this longer story about Power BI will get you going. If you’re a Power BI veteran, this articles will tie concepts together and fill in the gaps.
The parts of Power BI
Power BI consists of a Microsoft Windows desktop application called Power BI Desktop, an online SaaS (Software as a Service) service called the Power BI service, and mobile Power BI apps that are available on Windows phones and tablets, and also on Apple iOS and Google Android devices.
These three elements — Desktop, the service, and Mobile apps — are designed to let people create, share, and consume business insights in the way that serves them, or their role, most effectively.
How Power BI matches your role
How you use Power BI might depend on your role on a project or a team. And other people, in other roles, might use Power BI differently, which is just fine.
For example, you might view reports and dashboards in the Power BI service, and that might be all you do with Power BI. But your number-crunching, business-report-creating coworker might make extensive use of Power BI Desktop (and publish Power BI Desktop reports to the Power BI service, which you then use to view them). And another coworker, in sales, might mainly use her Power BI phone app to monitor progress on her sales quotas and drill into new sales lead details.
You also might use each element of Power BI at different times, depending on what you’re trying to achieve, or what your role is for a given project or effort.
Perhaps you view inventory and manufacturing progress in a real-time dashboard in the service, and also use Power BI Desktop to create reports for your own team about customer engagement statistics. How you use Power BI can depend on which feature or service of Power BI is the best tool for your situation. But each part of Power BI is available to you, which is why it’s so flexible and compelling.
We discuss these three elements — Desktop, the service, and Mobile apps — in more detail later. In upcoming units and modules, we’ll also create reports in Power BI Desktop, share them in the service, and eventually drill into them on our mobile device.
The flow of work in Power BI
A common flow of work in Power BI begins in Power BI Desktop, where a report is created. That report is then published to the Power BI service and finally shared, so that users of Power BI Mobile apps can consume the information.
It doesn’t always happen that way, and that’s okay. But we’ll use that flow to help you learn the different parts of Power BI and how they complement each other.
Okay, now that we have an overview of this module, what Power BI is, and its three main elements, let’s take a look at what it’s like to use Power BI.
Power BI Desktop is a free application for PCs that lets you gather, transform, and visualize your data. In this module, you’ll learn how to find and collect data from different sources and how to clean or transform it. You’ll also learn tricks to make data-gathering easier.
Power BI Desktop and the Power BI Service work together. You can create your reports and dashboards in Power BI Desktop, and then publish them to the Power BI Service for others to consume.
The following are the tasks that you will complete in this module:
Video: Introduction to Power BI Desktop
To perform the exercises in this module, you’ll need to have Power BI desktop installed and have a Power BI Service account set up.
Download Power BI Desktop
You can download Power BI Desktop from the web or as an app from the Microsoft Store on the Windows tab.
Let’s do a quick review of what we covered in this chapter.
Microsoft Power BI is a collection of software services, apps, and connectors that work together to turn your data into interactive insights. You can use data from single basic sources, like a Microsoft Excel workbook, or pull in data from multiple databases and cloud sources to create complex datasets and reports. Power BI can be as straightforward as you want or as enterprise-ready as your complex global business requires.
Power BI consists of three main elements—Power BI Desktop, the Power BI service, and Power BI Mobile—which work together to let you create, interact with, share, and consume your data the way you want.
We also discussed the basic building blocks in Power BI:
Visualizations – A visual representation of data, sometimes just called visuals
Datasets – A collection of data that Power BI uses to create visualizations
Reports – A collection of visuals from a dataset, spanning one or more pages
Dashboards – A single-page collection of visuals built from a report
Tiles – A single visualization on a report or dashboard
In the Power BI service, we installed an app in just a few clicks. That app, a ready-made collection of visuals and reports, let us easily connect to a software service to populate the app and bring that data to life.
Finally, we set up a refresh schedule for our data, so that we know the data will be fresh when we go back to the Power BI service.
Congratulations! You’ve finished the first chapter of the long story about Power BI. You now have a firm foundation of knowledge for when you move on to the next module, which walks through the steps to create your first report.
We mentioned this before, but it’s worth restating: this learning path builds your knowledge by following the common flow of work in Power BI:
Bring data into Power BI Desktop, and create a report.
Publish to the Power BI service, where you create new visualizations or build dashboards.
Share your dashboards with others, especially people who are on the go.
View and interact with shared dashboards and reports in Power BI Mobile apps.
You might not do all that work yourself—some people will only view dashboards that were created by someone else, and they’ll just use the service. That’s fine, and we’ll soon have a module dedicated to showing how you can easily navigate and use the Power BI service to view and interact with reports and apps.
But the next chapter follows the flow of work in Power BI, showing you how to create a report and publish it to the Power BI service. You’ll learn how those reports and dashboards are created and how they connected to the data. You might even decide to create a report or dashboard of your own.
As we learned in the previous unit, the common flow of work in Microsoft Power BI is to create a report in Power BI Desktop, publish it to the Power BI service, and then share it with others, so that they can view it in the service or on a mobile app.
But because some people begin in the Power BI service, let’s take a quick look at that first, and learn about an easy and popular way to quickly create visuals in Power BI: apps.
An app is a collection of preset, ready-made visuals and reports that are shared with an entire organization. Using an app is like microwaving a TV dinner or ordering a fast-food value meal: you just have to press a few buttons or make a few comments, and you’re quickly served a collection of entrees designed to go together, all presented in a tidy, ready-to-consume package.
So, let’s take a quick look at apps, the service, and how it works. We’ll go into more detail about apps (and the service) in upcoming modules, but you can think of this as a taste to whet your appetite.
Create out-of-box dashboards with cloud services
With Power BI, connecting to data is easy. From the Power BI service, you can just select the Get Data button in the lower-left corner of the home page.
The canvas (the area in the center of the Power BI service) shows you the available sources of data in the Power BI service. In addition to common data sources like Microsoft Excel files, databases, or Microsoft Azure data, Power BI can just as easily connect to a whole assortment of software services (also called SaaS providers or cloud services): Salesforce, Facebook, Google Analytics, and more.
For these software services, the Power BI service provides a collection of ready-made visuals that are pre-arranged on dashboards and reports for your organization. This collection of visuals is called an app. Apps get you up and running quickly, with data and dashboards that your organization has created for you. For example, when you use the GitHub app, Power BI connects to your GitHub account (after you provide your credentials) and then populates a predefined collection of visuals and dashboards in Power BI.
There are apps for all sorts of online services. The following image shows a page of apps that are available for different online services, in alphabetical order. This page is shown when you select the Get button in the Services box (shown in the previous image). As you can see from the following image, there are many apps to choose from.
For our purposes, we’ll choose GitHub. GitHub is an application for online source control. When you select the Get it now button in the box for the GitHub app, the Connect to GitHub dialog box appears. Note that Github does not support Internet Explorer, so make sure you are working in another browser.
After you enter the information and credentials for the GitHub app, installation of the app begins.
After the data is loaded, the predefined GitHub app dashboard appears.
In addition to the app dashboard, the report that was generated (as part of the GitHub app) and used to create the dashboard is available, as is the dataset (the collection of data pulled from GitHub) that was created during data import and used to create the GitHub report.
On the dashboard, you can select any of the visuals and interact with them. As you do so, all the other visuals on the page will respond. For example, when the May 2018 bar is selected in the Pull Requests (by month) visual, the other visuals on the page adjust to reflect that selection.
Update data in the Power BI service
You can also choose to update the dataset for an app, or other data that you use in Power BI. To set update settings, select the schedule update icon for the dataset to update, and then use the menu that appears. You can also select the update icon (the circle with an arrow) next to the schedule update icon to update the dataset immediately.
The Datasets tab is selected on the Settings page that appears. In the right pane, select the arrow next to Scheduled refresh to expand that section. The Settings dialog box appears on the canvas, letting you set the update settings that meet your needs.
That’s enough for our quick look at the Power BI service. There are many more things you can do with the service, and we’ll cover these later in this module and in upcoming modules. Also, remember that there are many types of data you can connect to, and all sorts of apps, with more of both coming all the time.
While Power BI can import your data from almost any source, its visualization and modeling tools work best with columnar data. Sometimes, your data won’t be formatted in simple columns, which is often the case with Excel spreadsheets.
In this unit, you will clean columnar data with Power Query Editor.
Video: Cleaning data
A table layout that looks good to the human eye might not be optimal for automated queries. For example, the following spreadsheet has headers that span multiple columns.
Fortunately, Power Query Editor has tools to help you quickly transform multi-column tables into datasets that you can use.
By using Transpose in Power Query Editor, you can swap rows into columns to better format the data.
You might need to format data so that Power BI can properly categorize and identify that data. With some transformations, you’ll cleanse data into a dataset that you can use in Power BI. Examples of powerful transformations include promoting rows into headers, using Fill to replace null values, and Unpivot Columns.
With Power BI, you can experiment with transformations and determine which will transform your data into the most usable columnar format. Remember, the Applied Steps section of Power Query Editor records all your actions. If a transformation doesn’t work the way that you intended, select the X next to the step, and then undo it.
After you’ve cleaned your data into a usable format, you can begin to create powerful visuals in Power BI.
Often, you’ll connect to multiple data sources to create your reports. All that data needs to work together to create a cohesive report. Modeling is how to get your connected data ready for use.
Tasks in this module:
Create relationships between your data sources
Create a new field with calculated columns
Optimize data by hiding fields and sorting visualization data
Create a measure to perform calculations on your data
Use a calculated table to create a relationship between two tables
Format time-based data so that you can drill down for more details
Video: Overview of modeling data
In Power BI, you can create a relationship to create a logical connection between different data sources. A relationship enables Power BI to connect tables to one another so that you can create visuals and reports. This module describes data-centric relationships and how to create relationships when none exists.
Note: One of Power BI’s strengths is that you don’t need to flatten your data into a single table. Instead, you can use multiple tables from multiple sources and define the relationship between them.
You’ll also create your own custom calculations and assign new metrics to view specific segments of your data.
The Model view in Power BI Desktop allows you to visually set the relationship between tables or elements. Use the Model view to see a diagrammatic view of your data.
Tasks in this module include:
Video: Manage data relationships
In the Model view, notice that a block represents each table and its columns and that lines between them represent relationships.
Adding and removing relationships is straightforward. To remove a relationship, right-click the relationship and select Delete. To create a relationship, drag and drop the fields that you want to link between tables.
To hide a table or individual column from your report, right-click the table or column in the Model view and select Hide in report view.
For a more detailed view of your data relationships, on the Home tab, select Manage Relationships. The Manage Relationships dialog box displays your relationships as a list instead of as a visual diagram. From the dialog box, you can select Autodetect to find relationships in new or updated data. Select Edit to manually edit your relationships. You’ll find advanced options in the Edit section to set the Cardinality and Cross-filter direction of your relationships.
Your options for Cardinality are explained in the following table.
Many to One
The most common default relationship. The column in one table can have more than one instance of a value. The related table (or lookup table) has only one instance of a value.
One to One
The column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value.
By default, you will set relationships to cross-filter in both directions. Cross-filtering in just one direction limits some of the modeling capabilities in a relationship.
Setting accurate relationships between your data allows you to create complex calculations across multiple data elements.
Sometimes, the data that you’re analyzing doesn’t contain a field that you need. The answer might be calculated columns. You can create a new calculated column by transforming two or more elements of existing data. For example, you can create a new column by combining two columns into one.
Tasks in this module include:
Video: Create calculated columns
One reason for creating a calculated column is to establish a relationship between tables when no unique fields exist. The lack of a relationship becomes obvious when you create a simple table visual in Power BI Desktop and get the same value for all entries.
For example, to create a relationship with unique fields in data, you can create a new calculated column for “CountryZip” by combining the values from the Country and the Zip columns.
To create a calculated column, select the Data view in Power BI Desktop from the left side of the report canvas.
From the Modeling tab, select New Column to enable the formula bar. You can enter calculations by using Data Analysis Expressions (DAX) language. DAX is a powerful formula language, also found in Excel, that lets you build robust calculations. As you type a formula, Power BI Desktop displays matching formulas or data elements to assist and accelerate the creation of your formula.
The Power BI formula bar will suggest specific DAX functions and related data columns as you enter your expression.
After you have created the calculated columns in each table, they can be used as a unique key to establish a relationship between them. By going to the Relationship view, you can then drag the field from one table to the other to create the relationship.
Imported data often contains fields that you don’t need for your reporting and visualization tasks. Data might contain unnecessary information or it might be available in another column. Power BI Desktop has tools to optimize your data and make it more usable for building reports and visuals.
Tasks in this module include:
Video: Optimize data models
To hide a field in the Fields pane of Power BI Desktop, right-click the column and select Hide. Your hidden fields aren’t deleted. If you’ve used a hidden field in existing visuals, the data is still there; the hidden field just isn’t displayed on the Fields pane.
If you view tables in the Model view, hidden fields appear dimmed. The data in these tables is still available and is still part of the model. You can unhide any field that has been hidden by right-clicking the field and then selecting unhide.
Sort visualization data by another field
The Sort by Column tool, available on the Modeling tab, is useful to help ensure that your data is displayed in the order that you intended.
As a common example, data that includes the name of the month is sorted alphabetically by default, for example, August would appear before February.
In this case, selecting the field in the Fields list, selecting Sort by Column from the Modeling tab, and then choosing a field to sort by can remedy the problem. The MonthNo category sort option will order the months as intended.
Setting the data type for a field is another way to optimize your information so that it’s handled correctly. To change a data type from the report canvas, select the column in the Fields pane, and then use the Format drop-down menu to select one of the formatting options. Any visuals you’ve created that display that field are updated automatically.