fbpx

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

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

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

Explore time-based data

Explore time-based data

Analyzing time-based data with Power BI is a simple process. The modeling tools in Power BI Desktop automatically generate fields that let you drill down through time periods.

Tasks in this module include:

Long-Story-About-Power-BI-3-Model-data-7-Explore-time-based-data-01

Video: Explore time based data

When you create a table visualization in your report by using a date field, Power BI Desktop automatically includes breakdowns by time period. For example, the single date field in the Date table was automatically separated into Year, Quarter, Month, and Day by Power BI.

Long-Story-About-Power-BI-3-Model-data-7-Explore-time-based-data-02

Visuals display data at the year level by default, but you can change that by turning on Drill Down in the top, right-hand corner of the visual.

Long-Story-About-Power-BI-3-Model-data-7-Explore-time-based-data-03

When you select the bars or lines in your chart, the system will drill down to the next level of time hierarchy, for example, from years to quarters. You can continue to drill down until you reach the most granular level of the hierarchy: days. To move back up through the time hierarchy, select Drill Up in the top, left-hand corner of the visual.

Long-Story-About-Power-BI-3-Model-data-7-Explore-time-based-data-04

You can also drill down through all the data that is shown on the visual instead of through one selected period. To do so, use the Go to the next level in the hierarchy double-arrow icon.

Long-Story-About-Power-BI-3-Model-data-7-Explore-time-based-data-05

As long as your model has a date field, Power BI will automatically generate different views for different time hierarchies.

Transform data to include in a report

Transform data to include in a report

Sometimes, your data might contain extra data or have data in the wrong format. Power BI Desktop includes the Power Query Editor tool, which can help you shape and transform data so that it’s ready for your models and visualizations.

In this chapter, you will transform data with Power Query Editor.

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

Video: Transform data

Launch Power Query Editor

To begin, select Edit from the Navigator window to launch Power Query Editor. You can also launch Power Query Editor directly from Power BI Desktop by using the Edit Queries button on the Home ribbon.

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

After loading your data into Power Query Editor, you’ll see the following screen.

Long-Story-About-Power-BI-2-Get-Data-5-Transform-data-to-include-in-a-report-03
  1. In the ribbon, the active buttons enable you to interact with the data in the query.
  2. On the left pane, queries (one for each table, or entity) are listed and available for selecting, viewing, and shaping.
  3. On the center pane, data from the selected query is displayed and available for shaping.
  4. The Query Settings window lists the query’s properties and applied steps.

Transform data

On the center pane, right-clicking a column displays the available transformations. Examples of the available transformations include removing a column from the table, duplicating the column under a new name, or replacing values. From this menu, you can also split text columns into multiples by common delimiters.

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

The Power Query Editor ribbon contains additional tools that can help you change the data type of columns, add scientific notation, or extract elements from dates, such as day of the week.

Tip: If you make a mistake, you can undo any step from the Applied Steps list.

Long-Story-About-Power-BI-2-Get-Data-5-Transform-data-to-include-in-a-report-05

After you select Close & Apply, Power Query Editor applies the query changes and applies them to Power BI Desktop.

For more information, see Quickstart: Using Power Query in Power BI Desktop

Get data from Excel

Get data from Excel

Likely, you’ve used Microsoft Excel to create or view reports or to build pie charts or other visuals. Getting your Excel data into Power BI is a straightforward process.

In this chapter, you will bring Excel workbooks into Power BI.

Long-Story-About-Power-BI-2-Get-Data-4-Get-data-from-Excel-01

Video:

This unit explains how you can import an Excel workbook file that contains a simple table from a local drive into Power BI. You’ll then learn how to begin exploring that table’s data in Power BI by creating a report.

Note: Up until now, we’ve been importing data through Power BI Desktop. This unit page is done from the Power BI service.

Make sure that your data is formatted as a table

For Power BI to import data from your workbook, that data needs to be formatted as a table. In Excel, you can highlight a range of cells, and then on the Insert tab of the Excel ribbon, select Table.

Long-Story-About-Power-BI-2-Get-Data-4-Get-data-from-Excel-02

Make sure that each column has a good name; it will make it easier for you to find the data that you want when creating your reports in Power BI.

Import from a local drive

Wherever you keep your files, Power BI makes importing them simple. In Power BI, you can go to Get Data > Files > Local File to select the Excel file that you want.

Long-Story-About-Power-BI-2-Get-Data-4-Get-data-from-Excel-03

After the file has been imported into Power BI, you can begin creating reports.

Your files don’t have to be on a local drive. If you save your files on OneDrive or SharePoint Team Site, that’s even better.

Create reports

After your workbook’s data has been imported, a dataset is created in Power BI and it will appear under Datasets.

Long-Story-About-Power-BI-2-Get-Data-4-Get-data-from-Excel-04

Now, you can begin exploring your data by creating reports and dashboards. Select the Open menu icon next to the dataset and then select Explore. A new blank report canvas appears. On the right-hand side, under Fields, are your tables and columns. Select the fields for which you want to create a new visualization on the canvas.

Long-Story-About-Power-BI-2-Get-Data-4-Get-data-from-Excel-05

You can change the type of visualization and apply filters and other properties under Visualizations.

If you use any of Excel’s advanced BI features like Power QueryPower Pivot, or Power View, you can import that data into Power BI, too.

For more information, see Get data from Excel workbook files.

Connect to data sources

Connect to data sources

Power BI Desktop connects to many types of data sources, including local databases, worksheets, and data on cloud services. Sometimes when you gather data, it’s not quite as structured, or clean, as you want it to be. To structure data, you can transform it, meaning that you can split and rename columns, change data types, and create relationships between columns.

In this chapter, you will:

  1. Connect to data.
  2. Import data into Power BI Desktop.
Long-Story-About-Power-BI-2-Get-Data-3-Connect-to-data-sources-01

Video: Connecting to data sources

You can connect Power BI Desktop to many types of data sources, including on-premises databases, Microsoft Excel workbooks, and cloud services. Currently, there are about 60 Power BI-specific connectors to cloud services such as GitHub and Marketo. You can also connect to generic sources through XML, CSV, text, and ODBC. Power BI will even extract tabular data directly from a website URL.

Connect to data

When you start Power BI Desktop, you can choose Get Data from the ribbon on the Home tab.

Long-Story-About-Power-BI-2-Get-Data-3-Connect-to-data-sources-02

In Power BI Desktop, several types of data sources are available. Select a source to establish a connection. Depending on your selection, you’ll be asked to find the source on your computer or network. You might be prompted to sign in to a service to authenticate your request.

Long-Story-About-Power-BI-2-Get-Data-3-Connect-to-data-sources-03

Choose data to import

After connecting, the first window that you’ll see is the Navigator. The Navigator window displays the tables or entities of your data source, and selecting a table or entity gives you a preview of its contents. You can then import your selected tables or entities immediately, or you can select Edit to transform and clean your data before importing.

Long-Story-About-Power-BI-2-Get-Data-3-Connect-to-data-sources-04

After you’ve selected the tables that you’d like to bring into Power BI Desktop, select the Load button. You might want to make changes to those tables before you load them. For example, if you only want a subset of customers or a specific country or region, select the Edit button and filter data before loading.

Long-Story-About-Power-BI-2-Get-Data-3-Connect-to-data-sources-05

No matter what type of data you need, you’re likely to find a way to import it into Power BI Desktop.

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