fbpx

Overview of Power BI Desktop

Overview of Power BI Desktop

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:

Long-Story-About-Power-BI-2-Get-Data-1-Overview-of-Power-BI-Desktop-01

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.

Download optionsLinkNotes
Power BI Desktop – Windows Store AppWindows StoreWill automatically stay updated
Power BI Desktop – Download from webDownload .msiMust manually update periodically

*Both versions of them are the same, the only difference is, how they stayed updated, automatically or manually

Sign in to Power BI service

Before you can sign in to Power BI, you’ll need an account. To get a free trial, go to app.powerbi.com and sign up with your email address.

For detailed steps on setting up an account, see Sign in to Power BI service.

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

Explore Power BI Desktop

Explore Power BI Desktop

The idea of building and sharing reports is an abstract concept. It will make more sense if you explore Power BI Desktop hands-on. The first step is to launch and explore the user interface (UI).

In this chapter, you will:

  1. Launch the Power BI Desktop.
  2. Explore the UI.
Long-Story-About-Power-BI-2-Get-Data-2-Explore-Power-BI-Desktop-01

Video: Overview of Power BI Desktop and the Power BI service

Launch Power BI Desktop

When you launch Power BI Desktop, the Getting Started dialog box will appear, which provides useful links to forums, blogs, and introductory videos. Close this dialog box for now, but keep the Show this screen on startup option selected so that you can explore it later.

Explore the report building environment

In Power BI Desktop, you’ll begin to build reports in the Report view. You’ll be working in five main areas:

Long-Story-About-Power-BI-2-Get-Data-2-Explore-Power-BI-Desktop-02
  1. Ribbon – Displays common tasks that are associated with reports and visualizations.
  2. Report view, or canvas – Where visualizations are created and arranged. You can switch between Report, Data, and Model views by selecting the icons in the left column.
  3. Pages tab – Located along the bottom of the page, this area is where you would select or add a report page.
  4. Visualizations pane – Where you can change visualizations, customize colors or axes, apply filters, drag fields, and more.
  5. Fields pane – Where query elements and filters can be dragged onto the Report view or dragged to the Filters area of the Visualizations pane.

Tip: You can collapse the Visualizations and Fields panes to provide more space in the Report view by selecting the small arrow, as shown in the following screenshot.

Long-Story-About-Power-BI-2-Get-Data-2-Explore-Power-BI-Desktop-03

Create a visual

To create a visual, drag a field from the Fields list onto the Report view.

Long-Story-About-Power-BI-2-Get-Data-2-Explore-Power-BI-Desktop-04

Power BI Desktop automatically created a map-based visualization because it recognized that the State field contained geo-location data.

Publish a report

After creating a report with a few visuals, you’re ready to publish to the Power BI service. On the Home ribbon on the Power BI Desktop, select Publish.

Long-Story-About-Power-BI-2-Get-Data-2-Explore-Power-BI-Desktop-05

You’ll be prompted to sign in to Power BI. When you’ve signed in and the publish process is complete, the following dialog box will appear. You can select the link below Success!, which will take you to the Power BI service, where you can see the report that you published.

Pin a visual to a dashboard

When you view a published report in the Power BI service, you can choose the Pin icon to pin that visual to a dashboard.

Long-Story-About-Power-BI-2-Get-Data-2-Explore-Power-BI-Desktop-06

You can choose whether to pin the visual to an existing dashboard or to create a new dashboard.

For more information, see Report View in Power BI Desktop .

Map visualizations

Map visualizations

Power BI has two different types of map visualizations: a bubble map that places a bubble over a geographic point, and a shape map that shows the outline of the area that you want to visualize.

Video: Map visuals

Long-Story-About-Power-BI-4-Use-visuals-data-4-Map-visualizations-01

Important: When you are working with countries or regions, use the three-letter abbreviation to ensure that geocoding works properly. Do not use two-letter abbreviations because some countries or regions might not be properly recognized.

If you only have two-letter abbreviations, go to this external blog post for steps on how to associate your two-letter country and/or region abbreviations with three-letter country and/or region abbreviations.

Create bubble maps

To create a bubble map, select the Map option in the Visualization pane. In the Visualizations options, add a value to the Location bucket to use a map visual.

Long-Story-About-Power-BI-4-Use-visuals-data-4-Map-visualizations-02

Power BI accepts many types of location values. It recognizes city names, airport codes, or specific latitude and longitude data. Add a field to the Size bucket to change the size of the bubble for each map location.

Long-Story-About-Power-BI-4-Use-visuals-data-4-Map-visualizations-03

Create shape maps

To create a shape map, select the Filled Map option in the Visualization pane. As with bubble maps, you must add a value to the Location bucket to use this visual. Add a field to the Size bucket to change the intensity of the fill color.

Long-Story-About-Power-BI-4-Use-visuals-data-4-Map-visualizations-05

A warning icon in the top-left corner of your visual indicates that the map needs more location data to accurately plot values. This is a common problem when the data in your location field is ambiguous, such as using an area name like Washington, which could indicate a state or a district.

One way to resolve the location data problem is to rename your column to be more specific, such as State. Another way is to manually reset the data category by selecting Data Category on the Modeling tab. From the Data Category list, you can assign a category to your data such as “State” or “City.”

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.

Matrices and tables

Matrices and tables

You can use Power BI Desktop to create graphical and tabular visuals.

Video: Create tables

If you have numerical information in a table, such as revenue, a total sum will appear at the bottom. You can manually sort by each column by selecting the column header to switch ascending or descending order. If a column isn’t wide enough to display all its contents, select and drag the column header to expand it.

In the Visualizations pane, the order of the fields in the Values bucket determines the order in which they appear in your table.

Long-Story-About-Power-BI-4-Use-visuals-data-5-Matrices-and-tables-01

A matrix is similar to a table, but it has different category headers on the columns and rows. As with tables, numerical information will be automatically totaled along the bottom and right side of the matrix.

Long-Story-About-Power-BI-4-Use-visuals-data-5-Matrices-and-tables-02

Many cosmetic options are available for matrices, such as auto-sizing columns, switching between row and column totals, setting colors, and more.

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.

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

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