fbpx

Create scatter, waterfall, and funnel charts

Create scatter, waterfall, and funnel charts

Use a scatter chart to compare two different measures, such as unit sales versus revenue.

Long-Story-About-Power-BI-4-Use-visuals-data-6-Create-scatter-waterfall-and-funnel-charts-01

To create a blank chart, select Scatter chart from the Visualizations pane. Drag and drop the two fields that you want to compare from the Fields pane to the X Axis and Y Axis option buckets. At this point, your scatter chart probably has a small bubble in the center of the visual. You need to add a measure to the Details bucket to indicate how you want to segment your data. For example, if you’re comparing item sales and revenue, you might want to split the data by category, or manufacturer, or month of sale.

Adding another field to the Legend bucket will color-code your bubbles according to the field’s value. You can also add a field to the Size bucket to alter the bubble size according to that value.

Long-Story-About-Power-BI-4-Use-visuals-data-6-Create-scatter-waterfall-and-funnel-charts-02

Scatter charts have many visual formatting options as well, such as turning on an outline for each colored bubble and switching between individual labels. You can change the data colors for other chart types as well.

Long-Story-About-Power-BI-4-Use-visuals-data-6-Create-scatter-waterfall-and-funnel-charts-03

You can create an animation of your bubble chart’s changes over time by adding a time-based field to the Play Axis bucket. Select a bubble during an animation to see a trace of its path.

Long-Story-About-Power-BI-4-Use-visuals-data-6-Create-scatter-waterfall-and-funnel-charts-04

Note: Remember, if you only see one bubble in your scatter chart, it’s because Power BI is aggregating your data, which is the default behavior. To get more bubbles, add a category to the Details bucket in the Visualizations pane.

Create waterfall and funnel charts

Waterfall and funnel charts are two of the more noteworthy (and uncommon) standard visualizations that are included in Power BI. To create a blank chart of either type, select its icon from the Visualizations pane.

Video: Create scatter charts

Long-Story-About-Power-BI-4-Use-visuals-data-6-Create-scatter-waterfall-and-funnel-charts-05

Waterfall charts are typically used to show changes in a specific value over time.

Long-Story-About-Power-BI-4-Use-visuals-data-6-Create-scatter-waterfall-and-funnel-charts-06

Waterfalls only have two bucket options: Category and Y Axis. Drag a time-based field, such as Year, to the Category bucket, and drag the value that you want to track to the Y Axis bucket. Time periods where an increase in value occurred are displayed in green by default, while periods with a decrease in value are displayed in red.

Funnel charts are typically used to show changes over a specific process, such as a sales pipeline or website retention efforts.

Long-Story-About-Power-BI-4-Use-visuals-data-6-Create-scatter-waterfall-and-funnel-charts-07

You can slice and customize Waterfall and Funnel charts.

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

Modify colors in charts and visuals

Modify colors in charts and visuals

Occasionally, you might want to modify the colors that are used in charts or visuals. Power BI gives you control over how colors are displayed. To begin, select a visual and then select the paintbrush icon in the Visualizations pane.

Video: Modify colors

Long-Story-About-Power-BI-4-Use-visuals-data-7-Modify-colors-in-charts-and-visuals-01.

Power BI provides many options for changing the colors or formatting the visual. You can change the color of all bars in a visual by selecting the color picker beside Default color and then selecting your color of choice.

Long-Story-About-Power-BI-4-Use-visuals-data-7-Modify-colors-in-charts-and-visuals-02

You can change the color of each bar (or other element, depending on the type of visual that you selected) by turning the Show all slider to On. A color selector will then appear for each element.

Conditional formatting

You can change the color based on a value or measure. To do so, select the vertical ellipsis next to Default color.

Long-Story-About-Power-BI-4-Use-visuals-data-7-Modify-colors-in-charts-and-visuals-03

The resulting visuals will be colored by the gradient that you select.

Long-Story-About-Power-BI-4-Use-visuals-data-7-Modify-colors-in-charts-and-visuals-04

You can use those values to create rules, for example, to set values above zero to a certain color and values below zero to another color.

In the Analytics pane, you can create many other lines for a visual, such as Min, Max, Average, Median, and Percentile lines.

Long-Story-About-Power-BI-4-Use-visuals-data-7-Modify-colors-in-charts-and-visuals-05

You can create a border around an individual visualization, and like other controls, you can specify the color of that border as well.

For more information, see Tips and tricks for color formatting in Power BI

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

Page layout and formatting

Page layout and formatting

Power BI Desktop gives you the ability to control the layout and formatting of your report pages, such as size and orientation.

Video: Format reports

Use the Page View menu from the View tab to change the way that your report pages scale. The available options include Fit To Page (default), Fit To Width, and Actual Size.

Long-Story-About-Power-BI-4-Use-visuals-data-8-Page-layout-and-formatting-01

You can also change the page size. By default, the report page size is set to 16:9. To change the page size, make sure that no visuals are selected, select the paintbrush icon on the Visualizations pane, and then select Page Size to expand that section.

Long-Story-About-Power-BI-4-Use-visuals-data-8-Page-layout-and-formatting-02

Options for page size include 4:3 (more of a square aspect ratio) and Dynamic (the page will stretch to fill the available space). A standard letter size option is available for reports as well. You might need to resize your visuals after changing the page size to ensure that they’re completely on the canvas.

You can specify a custom page size, setting the size by inches or pixels, and you can also change the background color of the entire report.

Another option is to select Cortana, which sizes the report so that it can be used as a result for searches that use Cortana.

Add static elements

Along with data-bound visuals, you can also add static elements such as text boxes, images, and shapes to improve the visual design of your reports. To add a visual element, select Text BoxImage, or Shapes from the Home tab.

Video: Add a visual element

Long-Story-About-Power-BI-4-Use-visuals-data-8-Page-layout-and-formatting-03

You can display large titles, captions, or short paragraphs in Text boxes, which can also include links and URLs.

Selecting Image will open a file browser where you can select the image from your computer or other networked source. By default, resizing an image in your report will maintain its aspect ratio.

You can insert five types of Shapes, including rectangles and arrows. Shapes can be opaque or transparent with a colored border. The latter is useful for creating borders around groups of visualizations.

Manage how elements overlap

When you have several elements on a report, Power BI lets you manage how they overlap with each other. This ordering of layers is known as the z-order.

Video: Manage the z-order of elements

To manage the z-order of elements in a report, select an element and use the Bring forward and Send backward buttons on the Visual tools tab.

Long-Story-About-Power-BI-4-Use-visuals-data-8-Page-layout-and-formatting-04

Reuse a report layout

Individual pages of a report can be complex, with multiple visualizations that interact in specific ways and have precise formatting. Occasionally, when building a report, you might want to use the same visuals and layouts for two different pages. For example, if you’ve just put together a report page on gross revenue, you might want an almost identical page on net revenue.

Recreating all your work would be difficult, but with Power BI Desktop, you can duplicate a report page.

Right-click the tab that you want to copy and then select Duplicate Page.

Long-Story-About-Power-BI-4-Use-visuals-data-8-Page-layout-and-formatting-05

For more information, see Tutorial: Adding formatting options to a Power BI custom visual

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

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