fbpx

Clean data to include in a report

Clean data to include in a report

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

Long-Story-About-Power-BI-2-Get-Data-7-Clean-data-to-include-in-a-report-01

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.

Long-Story-About-Power-BI-2-Get-Data-7-Clean-data-to-include-in-a-report-02

Clean data

Fortunately, Power Query Editor has tools to help you quickly transform multi-column tables into datasets that you can use.

Transpose data

By using Transpose in Power Query Editor, you can swap rows into columns to better format the data.

Long-Story-About-Power-BI-2-Get-Data-7-Clean-data-to-include-in-a-report-03

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

Long-Story-About-Power-BI-2-Get-Data-7-Clean-data-to-include-in-a-report-04

After you’ve cleaned your data into a usable format, you can begin to create powerful visuals in Power BI.

For more information, see Tutorial: Combine sales data from Excel and an OData feed

Introduction to modeling your data

Introduction to modeling your data

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:

  1. Create relationships between your data sources
  2. Create a new field with calculated columns
  3. Optimize data by hiding fields and sorting visualization data
  4. Create a measure to perform calculations on your data
  5. Use a calculated table to create a relationship between two tables
  6. Format time-based data so that you can drill down for more details
Long-Story-About-Power-BI-3-Model-data-1-Introduction-to-modeling-your-data-01

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.

How to manage your data relationships

How to manage your data relationships

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:

Long-Story-About-Power-BI-3-Model-data-1-Introduction-to-modeling-your-data-01

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.

Long-Story-About-Power-BI-3-Model-data-2-How-to-manage-your-data-relationships-02

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.

Long-Story-About-Power-BI-3-Model-data-2-How-to-manage-your-data-relationships-03

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.

Long-Story-About-Power-BI-3-Model-data-2-How-to-manage-your-data-relationships-04

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.

Long-Story-About-Power-BI-3-Model-data-2-How-to-manage-your-data-relationships-05

Your options for Cardinality are explained in the following table.

Cardinality options Example
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.

For more information, see: Create and manage relationships in Power BI Desktop

Create calculated columns

Create calculated columns

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:

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-01

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.

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-02

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.

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-03

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.

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-04

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.

Long-Story-About-Power-BI-3-Model-data-3-Create-calculated-columns-06

For more information on calculated columns, including the use of IF statements, see Tutorial: Create calculated columns in Power BI Desktop

Optimize data models

Optimize data models

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:

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-01

Video: Optimize data models

Hide fields

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.

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-02

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.

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-03

As a common example, data that includes the name of the month is sorted alphabetically by default, for example, August would appear before February.

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-04

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.

Long-Story-About-Power-BI-3-Model-data-4-Optimize-data-models-05

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.

For more information, see Sort by column in Power BI Desktop

Introduction to Power BI

Introduction to Power BI

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.

Long-Story-About-Power-BI-1-Get-Stared-1-Intoduction-01

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.

Long-Story-About-Power-BI-1-Get-Stared-1-Intoduction-02

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.

Create calculated measures

Create calculated measures

In Power BI, measures are defined calculations on your data that are performed at the time of your query. Measures are calculated as you interact with your reports and aren’t stored in your database.

Tasks in this module include:

Long-Story-About-Power-BI-3-Model-data-5-Create-calculated-measures-01

Video: Create calculated measures

Create a measure

To create a measure, in Report view, select New Measure from the Modeling tab.

Long-Story-About-Power-BI-3-Model-data-5-Create-calculated-measures-02

From the Formula bar, you can enter the DAX expression that defines your measure. As you enter your calculation, Power BI suggests relevant DAX functions and data fields. You’ll also receive a tooltip that explains some of the syntax and function parameters.

Long-Story-About-Power-BI-3-Model-data-5-Create-calculated-measures-03

If your calculation is long, you can add extra line breaks in the Expression Editor by typing ALT-Enter.

Long-Story-About-Power-BI-3-Model-data-5-Create-calculated-measures-04

Apply a measure

After you’ve created a new measure, it will appear in one of the tables on the Fields pane, which is found on the right side of the screen. Power BI inserts the new measure into whichever table you have currently selected. While it doesn’t matter, exactly, where the measure is located in your data, you can easily move it by selecting the measure and using the Home Table drop-down menu.

Long-Story-About-Power-BI-3-Model-data-5-Create-calculated-measures-05

You can use a measure like any other table column: just drag and drop it onto the report canvas or visualization fields. Measures also integrate seamlessly with slicers, segmenting your data on the fly, which means that you can define a measure once and then use it in many different visualizations.

For more information, see Tutorial: Create your own measures in Power BI Desktop

Use Power BI

Use Power BI

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:

  1. Bring data into Power BI Desktop, and create a report
  2. Publish to the Power BI service, where you can create new visualizations or build dashboards.
  3. Share dashboards with others, especially people who are on the go.
  4. View and interact with shared dashboards and reports in Power BI Mobile apps.
Long-Story-About-Power-BI-1-Get-Stared-2-Use-Power-BI-1

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.

Create calculated tables

Create calculated tables

Calculated tables is a function within DAX. Most of the time, you can import data into your model from an external data source. However, calculated tables provide intermediate calculations and data that you want stored as part of the model rather than as part of a query. You can use calculated tables, for example, to cross join two tables.

Tasks in this module include:

Long-Story-About-Power-BI-3-Model-data-6-Create-calculated-tables-01

Video: Create calculated measures

To create a calculated table, go to Data view in Power BI Desktop, which you can activate from the left side of the report canvas. Select New Table from the Modeling tab to open the formula bar.

Type the name of your new table, the equal sign, and the calculation that you want to use to form the table. Your new table will appear on the Fields pane in your model.

Long-Story-About-Power-BI-3-Model-data-6-Create-calculated-tables-02

After the new table has been created, you can use your calculated table as you would any other table in relationships, formulas, and reports.

For more information, see: Using calculated tables in Power BI Desktop

Building blocks of Power BI

Building blocks of Power BI

Everything you do in Microsoft Power BI can be broken down into a few basic building blocks. After you understand these building blocks, you can expand on each of them and begin creating elaborate and complex reports. After all, even seemingly complex things are built from basic building blocks. For example, buildings are created with wood, steel, concrete and glass, and cars are made from metal, fabric, and rubber. Of course, buildings and cars can also be basic or elaborate, depending on how those basic building blocks are arranged.

Let’s take a look at these basic building blocks, discuss some simple things that can be built with them, and then get a glimpse into how complex things can also be created.

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:

Here are the basic building blocks in Power BI:

  1. Visualizations
  2. Datasets
  3. Reports
  4. Dashboards
  5. Tiles

Visualizations

visualization (sometimes also referred to as a visual) is a visual representation of data, like a chart, a color-coded map, or other interesting things you can create to represent your data visually. Power BI has all sorts of visualization types, and more are coming all the time. The following image shows a collection of different visualizations that were created in the Power BI service.

Long-Story-About-Power-BI-1-Get-Stared-2-Building-blocks-of-Power-BI-1

Visualizations can be simple, like a single number that represents something significant, or they can be visually complex, like a gradient-colored map that shows voter sentiment about a certain social issue or concern. The goal of a visual is to present data in a way that provides context and insights, both of which would probably be difficult to discern from a raw table of numbers or text.

Datasets

dataset is a collection of data that Power BI uses to create its visualizations.

You can have a simple dataset that’s based on a single table from a Microsoft Excel workbook, similar to what’s shown in the following image.

Long-Story-About-Power-BI-1-Get-Stared-2-Building-blocks-of-Power-BI-2

Datasets can also be a combination of many different sources, which you can filter and combine to provide a unique collection of data (a dataset) for use in Power BI.

For example, you can create a dataset from three database fields, one website table, an Excel table, and online results of an email marketing campaign. That unique combination is still considered a single dataset, even though it was pulled together from many different sources.

Filtering data before bringing it into Power BI lets you focus on the data that matters to you. For example, you can filter your contact database so that only customers who received emails from the marketing campaign are included in the dataset. You can then create visuals based on that subset (the filtered collection) of customers who were included in the campaign. Filtering helps you focus your data—and your efforts.

An important and enabling part of Power BI is the multitude of data connectors that are included. Whether the data you want is in Excel or a Microsoft SQL Server database, in Azure or Oracle, or in a service like Facebook, Salesforce, or MailChimp, Power BI has built-in data connectors that let you easily connect to that data, filter it if necessary, and bring it into your dataset.

After you have a dataset, you can begin creating visualizations that show different portions of it in different ways, and gain insights based on what you see. That’s where reports come in.

Reports

In Power BI, a report is a collection of visualizations that appear together on one or more pages. Just like any other report you might create for a sales presentation or write for a school assignment, a report in Power BI is a collection of items that are related to each other. The following image shows a report in Power BI Desktop—in this case, it’s the fifth page in a six-page report. You can also create reports in the Power BI service.

Long-Story-About-Power-BI-1-Get-Stared-2-Building-blocks-of-Power-BI-3

Reports let you create many visualizations, on multiple pages if necessary, and let you arrange those visualization in whatever way best tells your story.

You might have a report about quarterly sales, product growth in a particular segment, or migration patterns of polar bears. Whatever your subject, reports let you gather and organize your visualizations onto one page (or more).

Dashboards

When you’re ready to share a single page from a report, or a collection of visualizations, you create a dashboard. Much like the dashboard in a car, a Power BI dashboard is a collection of visuals from a single page that you can share with others. Often, it’s a selected group of visuals that provide quick insight into the data or story you’re trying to present.

A dashboard must fit on a single page, often called a canvas (the canvas is the blank backdrop in Power BI Desktop or the service, where you put visualizations). Think of it like the canvas that an artist or painter uses — a workspace where you create, combine, and rework interesting and compelling visuals. You can share dashboards with other users or groups, who can then interact with your dashboards when they’re in the Power BI service or on their mobile device.

Tiles

In Power BI, a tile is a single visualization on a report or a dashboard. It’s the rectangular box that holds an individual visual. In the following image, you see one tile (highlighted by a bright box), which is also surrounded by other tiles.

Long-Story-About-Power-BI-1-Get-Stared-2-Building-blocks-of-Power-BI-4

When you’re creating a report or a dashboard in Power BI, you can move or arrange tiles however you want. You can make them bigger, change their height or width, and snuggle them up to other tiles.

When you’re viewing, or consuming, a dashboard or report—which means you’re not the creator or owner, but the report or dashboard has been shared with you — you can interact with it, but you can’t change the size of the tiles or their arrangement.

All together now

Those are the basics of Power BI and its building blocks. Let’s take a moment to review.

Power BI is a collection of services, apps, and connectors that lets you connect to your data, wherever it happens to reside, filter it if necessary, and then bring it into Power BI to create compelling visualizations that you can share with others.

Now that you’ve learned about the handful of basic building blocks of Power BI, it should be clear that you can create datasets that make sense to you and create visually compelling reports that tell your story. Stories told with Power BI don’t have to be complex, or complicated, to be compelling.

For some people, using a single Excel table in a dataset and then sharing a dashboard with their team will be an incredibly valuable way to use Power BI.

For others, the value of Power BI will be in using real-time Azure SQL Data Warehouse tables that combine with other databases and real-time sources to build a moment-by-moment dataset.

For both groups, the process is the same: create datasets, build compelling visuals, and share them with others. And the result is also the same for both groups: harness your ever-expanding world of data, and turn it into actionable insights.

Whether your data insights require straightforward or complex datasets, Power BI helps you get started quickly and can expand with your needs to be as complex as your world of data requires. And because Power BI is a Microsoft product, you can count on it being robust, extensible, Microsoft Office–friendly, and enterprise-ready.

Now let’s see how this works. We’ll start by taking a quick look at the Power BI service.

"The purpose of a business is to create a customer who creates customers" — Shiv Singh

Our reference list:

Liked our service? Start a project with us.