Introduction to Power Query

Power Query is a powerful data transformation and data preparation engine that is built into Power BI and other Microsoft products. It allows you to connect to various data sources, shape and transform your data, and then load it into your Power BI model for analysis and visualization.

Why Use Power Query?

  • Connect to Diverse Data Sources: Power Query can connect to hundreds of data sources, including files (Excel, CSV, JSON, XML), databases (SQL Server, Oracle, MySQL), cloud services (Azure SQL Database, SharePoint), and web pages.
  • Clean and Transform Data: It provides a user-friendly interface with a rich set of transformations such as filtering, sorting, removing columns, merging queries, appending queries, pivoting, unpivoting, and data type conversions.
  • Automate Data Preparation: Once you define your transformations, Power Query records them as steps. These steps can be reapplied automatically every time the data is refreshed, saving you significant manual effort.
  • Handle Complex Data: It's adept at handling messy, unstructured, or semi-structured data and preparing it for analysis.

Getting Started with Power Query Editor

The primary interface for Power Query is the Power Query Editor. You can access it from Power BI Desktop by clicking Transform data on the Home ribbon.

Connecting to Data

To start, click Get Data in the Power Query Editor. You'll see a wide range of connectors. Select your desired source and follow the prompts to connect.

For example, to connect to an Excel file:

  1. Click Get Data > Excel Workbook.
  2. Browse to and select your Excel file.
  3. In the Navigator window, select the sheet or table you want to import.
  4. Click Transform Data to open the Power Query Editor with your data.

Common Transformations

Once your data is loaded into the Power Query Editor, you can start transforming it using the ribbon commands. Here are some fundamental transformations:

1. Removing Columns

Often, you'll have columns that are not needed for your analysis. To remove them:

  • Select the column(s) you want to remove.
  • On the Home ribbon, click Remove Columns > Remove Columns or Remove Other Columns.

2. Filtering Rows

Filter rows based on specific criteria to narrow down your dataset.

  • Click the filter dropdown arrow on the column header.
  • Choose your filtering condition (e.g., Text Filters, Number Filters).
  • Enter the values or conditions.

3. Changing Data Types

Ensure your columns have the correct data types (e.g., Text, Whole Number, Decimal Number, Date). Incorrect data types can lead to errors during analysis.

  • Select the column.
  • On the Home ribbon, in the Transform group, click Data Type and choose the correct type.
  • Alternatively, click the icon on the left of the column header.

4. Renaming Columns

Clear and descriptive column names are crucial for understanding your data.

  • Double-click the column header and type the new name.
  • Alternatively, right-click the column header and select Rename.

5. Merging Queries

Combine data from two different queries based on a common column, similar to a SQL JOIN.

  • Go to the Home ribbon and click Merge Queries.
  • Select the primary query and the table query you want to merge with.
  • Select the common column(s) in both tables.
  • Choose the Join Kind (e.g., Left Outer, Inner).
  • Click OK.
  • Expand the merged column to select the columns you want to bring into your primary query.

6. Appending Queries

Combine rows from multiple queries with the same or similar structure into a single query.

  • Go to the Home ribbon and click Append Queries.
  • Select the tables you want to append.
  • Click OK.
Tip: All transformations you apply are recorded in the Applied Steps pane on the right side of the Power Query Editor. You can edit, delete, or reorder these steps as needed.

Loading Data

Once you're satisfied with your data transformations, you can load the data into Power BI:

  • Click Close & Apply on the Home ribbon of the Power Query Editor. This will load your transformed data into the Power BI data model, ready for visualization.

Next Steps

Mastering Power Query is essential for effective data preparation in Power BI. Continue to explore advanced transformations like grouping, pivoting, unpivoting, and using custom M language functions to further refine your data.