Power BI Data Preparation and Transformation with Power Query

Introduction

Power Query is a powerful data connectivity and preparation technology integrated into Microsoft products such as *Power BI, Excel, Analysis Services, and other Microsoft data solutions. It provides a consistent environment for connecting to different data sources, inspecting datasets, and transforming raw information into a structured format suitable for analysis.

The Power Query Editor is the primary workspace for preparing data. It provides a wide range of transformation capabilities that allow you to clean, reshape, combine, and validate data through an intuitive user interface.

One of the key advantages of Power Query is that its transformation techniques can be applied across different types of data sources. Whether your data comes from a database, spreadsheet, text file, web service, or cloud platform, Power Query provides tools for preparing that information before it is loaded into the Power BI data model.

Uses of Power Query

Power Query can be used to perform a wide variety of data preparation tasks, including:

• Connecting to multiple data sources such as databases, files, websites, APIs, and cloud storage
• Extracting and combining information from different sources
• Merging and appending datasets
• Adding, removing, and reorganizing columns
• Filtering, removing, and modifying rows
• Changing data types and correcting data formats
• Splitting and combining columns
• Pivoting and unpivoting data
• Transposing tables
• Creating custom columns and transformation logic
• Replacing errors, null values, and inconsistent entries
• Applying formulas using the Power Query M language
• Refreshing and publishing transformed datasets

These capabilities make Power Query an important component of the Power BI data preparation process.

Opening Power Query Editor in Power BI

Power Query is integrated directly into Power BI Desktop. You can access the Power Query Editor by selecting Transform Data from the Home tab.

Selecting Transform Data opens the Power Query Editor, where you can inspect and prepare your data before loading it into the Power BI model.

You can also connect to additional data sources directly from the Power Query Editor by selecting New Source and choosing the required connector, such as Text/CSV.

Power Query Editor Interface

The Power Query Editor provides a dedicated workspace for managing queries and performing data transformations. Its ribbon contains several tabs that organize the available commands and transformation features.

The commonly used tabs include:

• Home – Data source connections, query management, combining data, and loading options
• Transform – Data type changes, formatting, filtering, and other transformation operations
• Add Column – Creating custom, conditional, index, and other calculated columns
• View – Options for displaying formulas, layouts, and data profiling tools
• Tools – Additional query and development-related functionality
• Help – Documentation and assistance for Power Query features

Home

Transform

Add Column

View

Tools

Help

Opening Power Query During Data Import

Power Query can also be accessed while connecting to a new data source.

For example, when importing a CSV file into Power BI, you may be presented with options to either Load the data immediately or Transform Data before loading it.

Selecting Transform Data opens the Power Query Editor, where you can prepare the dataset by:

• Removing unnecessary columns
• Filtering unwanted records
• Changing data types
• Creating calculated or custom columns
• Replacing incorrect values
• Restructuring the dataset

Similarly, Power Query can be used to prepare data from Excel workbooks, SQL Server databases, JSON files, PDFs, SharePoint folders, and many other supported sources.

Performing Data Transformations

Power Query supports both basic and advanced data transformation operations. These transformations are applied as individual steps, allowing you to review, modify, and reorder the preparation process when necessary.

Common transformations include changing data types, removing or renaming columns, filtering rows, splitting fields, creating custom columns, and reshaping tables.

These capabilities allow raw data to be converted into a clean, structured, consistent, and analysis-ready dataset before it is loaded into the Power BI data model.

Creating Duplicate Queries in Power Query Editor

Power Query allows you to create a copy of an existing query without reconnecting to the original data source. This is useful when you want to apply different transformations to the same dataset while keeping the original query unchanged.

For this example, we will use a CSV file as the data source.

After selecting the file, choose Open to continue with the import process. Instead of loading the data immediately, select Transform Data.

Power Query Editor will open, displaying the imported dataset and providing access to the available transformation tools.

Creating a Duplicate Query

In the Queries pane, select the query you want to copy.

Then:

1. Go to the Home tab.
2. Select the Manage drop-down menu.
3. You will find options such as Delete, Duplicate, and Reference.
4. Select Duplicate.

Power Query creates a new query based on the selected query. The duplicate appears in the Queries pane and can be transformed independently.

A duplicate query is useful when you need to create multiple versions of the same dataset for different transformation or reporting requirements.

To remove the duplicate, select the query and use Home → Manage → Delete, or right-click the query and select Delete.

Sorting Data in Power Query

Power Query also allows you to arrange records based on the values in a selected column.

To sort a column:

1. Select the column you want to use for sorting.
2. Go to the Home tab.
3. Select Sort.
4. Choose either Ascending or Descending.

For example, the BusinessEntityID column can be sorted in ascending order to arrange the records from the smallest value to the largest.

Applying Your Changes

After completing the required transformations, select File → Close & Apply to save the changes and return to Power BI Desktop.

Power BI will apply the transformation steps and load the prepared data into the model.

Summary

Power Query provides practical tools for creating and managing queries, preparing datasets, and organizing records. Creating duplicate queries is particularly useful when the same source data needs to be transformed in different ways, while sorting helps organize records for easier inspection and analysis.