fbpx

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.

Introduction to visuals in Power BI

Introduction to visuals in Power BI

Visuals allow you to present data in a compelling and insightful way, and help you show the important components of it. Power BI has many compelling visuals and many more that are released frequently.

The following are the tasks that you’ll complete in this chapter:

  1. Explore Power BI visuals
  2. Create visuals
Long-Story-About-Power-BI-4-Use-visuals-data-1-Introduction-to-visuals-in-Power-BI-01

Video: Visuals in Power BI

This unit begins with the mainstays of visualizations, the simple visuals that everyone’s familiar with, to make sure that you know the particulars of them. The rest of the module will provide more advanced, or at least less common, details to enhance your report-creating knowledge.

Long-Story-About-Power-BI-4-Use-visuals-data-1-Introduction-to-visuals-in-Power-BI-02

Visualizing data is one of the core parts and basic building blocks of Power BI. Creating visuals is one of the most effective ways to find and share your insights.

You’ll discover a wide variety of visualizations in Power BI, which offers features such as simple bar charts, pie charts, maps, and more esoteric offerings like waterfalls, funnels, and gauges. Power BI Desktop also offers extensive page formatting tools, such as shapes and images, that help bring your report to life.

Create and customize simple visualizations

Create and customize simple visualizations

This unit explains how to create new bar charts, pie charts, and tree maps, and how to customize these elements to suit your reports.

Video: Create simple visuals

Two ways to create a new visualization in Power BI Desktop are:

  • Drag field names from the Fields pane and then drop them on the report canvas. By default, your visualization appears as a table of data.
Long-Story-About-Power-BI-4-Use-visuals-data-2-Create-and-customize-simple-visualizations-01
  • In the Visualizations pane, select the type of visualization that you want to create. With this method, the default visual is a blank placeholder that resembles the type of visual that you selected.
Long-Story-About-Power-BI-4-Use-visuals-data-2-Create-and-customize-simple-visualizations-02

After you have created your graph, map, or chart, you can begin dragging data fields onto the bottom portion of the Visualization pane to build and organize your visual. The available fields will change based on the type of visualization that you selected. As you drag and drop data fields, your visualization will automatically update to reflect changes.

Long-Story-About-Power-BI-4-Use-visuals-data-2-Create-and-customize-simple-visualizations-03

You can resize your visual by selecting it and then dragging the handles in or out. You can also move your visualization anywhere on the canvas by selecting and then dragging it to where you want it. If you want to convert between different types of visuals, select the visual that you want to change and select a different visual from the Visualization pane. Power BI attempts to convert your selected fields to the new visual type as closely as possible.

As you hover over parts of your visuals, you’ll receive a tooltip that contains details about that segment, such as labels and total value.

Select the paintbrush icon on the Visualizations pane to make cosmetic changes to your visual. Examples of cosmetic changes include background alignment, title text, and data colors.

Long-Story-About-Power-BI-4-Use-visuals-data-2-Create-and-customize-simple-visualizations-04

The available options for cosmetic changes to your visual vary depending on the type of visual that you’ve selected.

Note: Generally, visuals are used to compare two or more different values. However, sometimes when you are building reports, you might want to track a single metric over time. For more information, see Radial gauge charts in Power BI 

Create combination charts

Combination charts are an effective way to visualize multiple measures that have different scales in a single visualization.

Video: Combination charts

You might want to visualize two measures with different scales, such as revenue and units. Use a combination chart to show a line and a bar with different axis scales. Power BI supports many different types of combination charts by default, including Line and Stacked Columns charts.

You can split each column by category by dragging a category into the Column Series field. When you do so, each bar is proportionately colored based on the values within each category.

Combine data from multiple sources

Combine data from multiple sources

With Power BI Desktop, you can use the Power Query Editor tool to combine data from multiple sources into a single report.

In this chapter , you will combine data from different sources by using Query Editor.

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

Video: Modeling and transforming data

Add more data sources

To add more sources to an existing report, from the Home ribbon, select Edit Queries and then select New Source. You can use many potential data sources in Power BI Desktop, including folders. By connecting to a folder, you can import data from multiple Excel or CSV files at once.

Power Query Editor allows you to apply filters to your data. For example, selecting the drop-down arrow next to a column opens a checklist of text filters. Clearing a filter allows you to remove values from your model before the data is loaded into Power BI.

Long-Story-About-Power-BI-2-Get-Data-6-Combine-data-from-multiple-sources-02

Important: Filtering in the Power Query Editor changes which data is loaded into Power BI. Later, when you apply filters in the Data View or Report View, those filters only apply to what you see in visuals but do not change the underlying dataset.

Merge and append queries

You can also merge and append queries. In other words, Power BI pulls data that you select from multiple tables or various files into a single table. Use the Append Queries tool to add the data from a new table to an existing query. Power BI Desktop attempts to match the columns in your queries, which you can then adjust as necessary in Power Query Editor.

Long-Story-About-Power-BI-2-Get-Data-6-Combine-data-from-multiple-sources-03

Write customized queries

You can use the Add Custom Column tool to write new customized query expressions by using the powerful M language.

Long-Story-About-Power-BI-2-Get-Data-6-Combine-data-from-multiple-sources-04

For more information, see Tutorial: Shape and combine data in Power BI Desktop

Create slicers

Create slicers

Slicers are one of the most powerful types of visualizations, particularly as part of a busy report. A slicer is an on-canvas visual filter that allows report users to segment the data by a specific value. Examples of filters include by year or by geographical location.

Video: Create slicers

To add a slicer to your report, select Slicer from the Visualizations pane.

Long-Story-About-Power-BI-4-Use-visuals-data-3-Create-slicers-01

Drag the field by which you want to slice and drop it to the top of the slicer placeholder. The visualization turns into a list of elements with check boxes. These elements are your filters. Select the box next to the one that you want to segment, and Power BI will filter, or slice, all other visuals on the same report page.

Long-Story-About-Power-BI-4-Use-visuals-data-3-Create-slicers-02

A few different options are available to help you format your slicer. You can set it to accept multiple inputs at once, or you can use the Single Select mode to use one at a time. You can also add a Select All option to your slicer elements, which is helpful when you have a long list. Change the orientation of your slicer from the vertical default to horizontal, and it becomes a selection bar rather than a checklist.

Long-Story-About-Power-BI-4-Use-visuals-data-3-Create-slicers-03

When you have multiple visualizations on the same report page, Power BI Desktop lets you control how interactions flow between visuals. For more information, see Change how visuals interact in a Power BI report

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

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