fbpx

Category: Get data with Power BI Desktop

Connect to data sources

Posted on by 360 Reports

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.

Next page

Overview of Power BI Desktop

Posted on by 360 Reports

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.

Next page

Explore Power BI Desktop

Posted on by 360 Reports

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 .

Next page

Clean data to include in a report

Posted on by 360 Reports

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

Next chapter – Model Data in Power BI

Combine data from multiple sources

Posted on by 360 Reports

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

Next page

Transform data to include in a report

Posted on by 360 Reports

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

Next page

Get data from Excel

Posted on by 360 Reports

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 Query, Power Pivot, or Power View, you can import that data into Power BI, too.

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

Next page

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

Our reference list:
Henkel - Nederlands KaltePartner - Deutschland Ecosulis - United Kingdom American Express Global Servicing Network - India Carvi - United States BCM One - United States Frame Marketing

Liked our service? Start a project with us.