Understanding Queries, Applied Steps, and Transformation History in Power BI

Course: Microsoft Power BI Training

Topic: Understanding Queries, Applied Steps, and Transformation History

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction

Power BI is a business intelligence and data visualization platform used to connect, transform, analyze, and visualize data from different sources. Before creating reports and dashboards, data often needs to be cleaned, organized, and converted into a suitable format.

Power BI provides Power Query Editor to perform these data preparation tasks. Within Power Query Editor, three important concepts are Queries, Applied Steps, and Transformation History. Understanding these concepts helps users manage data preparation processes, troubleshoot errors, repeat transformations, and maintain consistent reporting workflows.

A query defines how data is obtained and prepared. Applied Steps represent the individual transformations performed on the data. Transformation history provides a sequence of these operations so users can review, modify, or remove them when necessary.

Learning Objectives

• Understand the meaning and purpose of queries in Power BI.
• Learn how to create, rename, duplicate, reference, and organize queries.
• Understand the Applied Steps section in Power Query Editor.
• Learn how to add, edit, reorder, and remove transformation steps.
• Understand the relationship between queries, steps, and Power Query M code.
• Learn how to troubleshoot common transformation errors.
• Apply best practices for creating maintainable data preparation workflows.

2. What Is a Query in Power BI?

A query is a set of instructions that tells Power BI how to connect to a data source, retrieve data, and prepare it for analysis. Queries are managed through Power Query Editor.

A query can connect to sources such as Excel workbooks, CSV files, SQL databases, SharePoint, web sources, and other supported data systems. After connecting, you can transform the retrieved data before loading it into the Power BI data model.

2.1 Example of a Query

Suppose a company stores sales information in an Excel file with the following columns:

A Power BI query can retrieve this table and apply transformations such as renaming columns, changing data types, removing duplicates, and filtering records.

2.2 Main Components of a Query

• Data source: The original location from which data is retrieved.
• Navigation: Identifies the specific table, sheet, view, or object to import.
• Transformations: Instructions for cleaning and modifying the data.
• Output: The final table produced by the query.
• Load settings: Determine whether the query's output is loaded into the Power BI data model.

2.3 Types of Query Operations

3. What Is Power Query Editor?

Power Query Editor is the data preparation environment in Power BI Desktop. It allows users to connect to data sources and perform transformations before the data is loaded into the model.

3.1 Opening Power Query Editor

1. Open Power BI Desktop.
2. Open an existing report or create a new report.
3. Select Home > Transform data.
4. Power Query Editor opens in a separate editing window.
5. Select a query from the Queries pane on the left.
6. Review the data preview and the Query Settings pane.
7. Locate the Applied Steps section to inspect the transformations.

3.2 Important Areas of Power Query Editor

• Queries pane: Lists the queries available in the current Power Query editing session.
• Data preview: Displays a preview of the selected query's data.
• Ribbon: Contains commands for managing columns, rows, data types, and transformations.
• Formula bar: Displays the Power Query M expression associated with the selected step, when enabled.
• Query Settings: Contains query properties, including the query name and Applied Steps.
• Advanced Editor: Displays the query's underlying Power Query M script.

4. Understanding Applied Steps

Applied Steps is a section in the Query Settings pane that lists the transformation operations applied to a selected query. Each step represents an operation in the data preparation process.

Power Query generally evaluates these steps in sequence. The output of one step becomes the input for the next step, allowing users to build a repeatable transformation workflow.

4.1 Example of Applied Steps

Suppose the original sales table contains unnecessary columns, inconsistent column names, and records from multiple regions. You want to prepare the data for a regional sales report.

4.2 Why Applied Steps Are Important

• Transparency: You can see how the original data was transformed.
• Repeatability: Power Query can reapply the defined transformations when the query is refreshed.
• Maintainability: Individual steps can be modified when requirements change.
• Error detection: You can identify the step where an error first appears.
• Flexibility: You can insert, remove, or adjust supported transformations.
• Auditability: The sequence helps users understand the logic used to prepare the data.

5. Understanding Transformation History

Transformation history refers to the sequence of data preparation operations represented by the query's Applied Steps. It provides a practical record of the transformations defined in the query.

For example, a query might import a workbook, promote headers, change data types, remove blank rows, filter records, and rename columns. The Applied Steps list shows these operations in their execution order.

5.1 Transformation History Example

Consider an employee dataset containing EmployeeID, EmployeeName, Department, Salary, and JoiningDate. The original data includes blank rows, inconsistent data types, and unnecessary fields.

1. Source: Connect to the employee workbook.
2. Navigation: Select the employee worksheet.
3. Promoted Headers: Convert the first row into column names.
4. Changed Type: Set Salary to a numeric type and JoiningDate to Date.
5. Removed Blank Rows: Remove completely blank rows.
6. Removed Columns: Delete fields that are not required.
7. Filtered Rows: Retain employees from selected departments.
8. Renamed Columns: Apply consistent, meaningful column names.

These steps create a documented sequence of transformations that can be reviewed and adjusted later.

5.2 Is Transformation History a Separate Permanent Log?

In the context of Power Query Editor, transformation history generally refers to the Applied Steps maintained in the query definition. It should not be confused with a permanent audit log that records every historical edit, every refresh, or every user action.

For long-term change tracking, organizations may need additional controls, such as version control for Power Query M scripts, documented change procedures, or appropriate Power BI workspace and deployment practices.

6. Relationship Between Queries, Applied Steps, and Transformation History

These three concepts are closely related but have different purposes.

Simple explanation: A query is the overall recipe, Applied Steps are the individual instructions in the recipe, and transformation history is the ordered sequence of those instructions.

7. How to Add and Manage Applied Steps

7.1 Adding a Transformation Step

1. Open Power Query Editor.
2. Select the required query from the Queries pane.
3. Select a column or rows in the data preview.
4. Choose a transformation from the ribbon or right-click menu.
5. Configure the transformation, such as a filter condition or data type.
6. Confirm the operation.
7. Review the new step in Applied Steps.
8. Check the data preview to verify the result.

7.2 Renaming an Applied Step

Renaming steps can make a complicated query easier to understand.

1. Locate the step in Applied Steps.
2. Right-click the step and select Rename, or use the available renaming command.
3. Enter a descriptive name.
4. Press Enter to confirm.

For example, rename a generic step such as Filtered Rows to Filter West Region Sales when that description accurately represents the operation.

7.3 Editing an Existing Step

1. Select the step you want to modify.
2. If the step has a settings icon, select it to reopen its configuration.
3. Change the condition, selected columns, or other available settings.
4. Confirm the changes.
5. Review the updated data preview and subsequent steps.

Some transformations do not provide a settings dialog. In such cases, you may need to edit the formula in the formula bar or modify the corresponding expression in Advanced Editor.

7.4 Deleting an Applied Step

1. Locate the unwanted step in Applied Steps.
2. Select the delete icon beside the step, or right-click it and choose Delete.
3. Confirm the operation if prompted.
4. Inspect the resulting preview and any later steps.

Important: Removing a step can change or break later steps if they depend on columns, values, or structures created by the deleted operation. Review downstream transformations after deletion.

7.5 Reordering Steps

Some transformations can be reordered, but not every reordering is valid or produces the same result. Moving a step earlier or later can affect the data available to subsequent operations.

For example, filtering rows before removing irrelevant columns may produce the same final result in some cases. However, filtering a column after that column has been removed will not work unless the query is changed accordingly.

8. Understanding the Power Query Formula Bar

The formula bar displays the Power Query M expression for the selected step when the formula bar is enabled. It helps users understand and modify the logic behind individual transformations.

8.1 Example Formula

= Table.SelectRows(Source, each [Region] = "West")

This expression filters the table represented by Source and retains rows where the Region column equals West.

8.2 Common M Functions

9. Understanding the Advanced Editor and M Code

Power Query uses the M language, also known as Power Query Formula Language, to define data retrieval and transformation operations.

While Applied Steps provides a graphical interface for working with transformations, Advanced Editor displays the underlying M script. Users can inspect the complete query logic and make advanced modifications when necessary.

9.1 Example of a Complete M Query

let
    Source = Excel.Workbook(
        File.Contents("C:\Data\Sales.xlsx"),
        null,
        true
    ),
    SalesSheet = Source{[Item="Sales", Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(
        SalesSheet,
        [PromoteAllScalars=true]
    ),
    ChangedType = Table.TransformColumnTypes(
        PromotedHeaders,
        {
            {"Order ID", Int64.Type},
            {"Customer", type text},
            {"Region", type text},
            {"Sales", type number}
        }
    ),
    FilteredRows = Table.SelectRows(
        ChangedType,
        each [Region] = "West"
    ),
    SelectedColumns = Table.SelectColumns(
        FilteredRows,
        {"Order ID", "Customer", "Region", "Sales"}
    )
in
    SelectedColumns

Note: This example assumes the workbook contains a worksheet named Sales and the listed columns exist after promoting headers. Update the file path, worksheet name, and column names to match your actual data source.

9.2 Understanding the M Query Structure

• let: Begins the sequence of variable and transformation definitions.
• Source: Represents the first step that reads the workbook.
• SalesSheet: Selects the required worksheet data.
• PromotedHeaders: Converts the first row into column headers.
• ChangedType: Assigns appropriate data types.
• FilteredRows: Keeps records where Region is West.
• SelectedColumns: Retains the required columns.
• in: Specifies the final result returned by the query.

Each named step refers to a previous step or expression. The final expression after in determines the query output.

10. Practical Example: Cleaning Sales Data

Imagine a company receives a monthly sales spreadsheet containing the following records:

10.1 Business Requirement

• Keep only sales from the West region.
• Remove the Remarks column.
• Ensure Sales is a numeric column.
• Use consistent column names.
• Prepare the final dataset for reporting.

10.2 Step-by-Step Transformation

1. Connect to the source: Import the Excel file into Power BI.
2. Promote headers: Make sure the first row contains the correct column names.
3. Set data types: Assign OrderID a whole-number type, Customer and Region text types, and Sales a numeric type.
4. Remove Remarks: Select the Remarks column and use Remove Columns.
5. Filter Region: Open the Region filter and select West.
6. Rename columns if necessary: Use consistent names suitable for the report.
7. Validate the result: Check the rows and column types in the preview.
8. Load the data: Select Close & Apply.

10.3 Expected Output

The final query contains only the required West region records and excludes the Remarks column. The Applied Steps section preserves the transformation sequence used to produce this output.

11. Refreshing Queries and Reapplying Transformations

One of the main advantages of Power Query is that its transformation instructions can be reused when new source data becomes available.

11.1 How Refresh Works

1. Connect Power BI to a data source.
2. Define the required transformations in Power Query Editor.
3. Apply the transformations and load the query output.
4. When the source data changes, refresh the data using the appropriate refresh operation.
5. Power Query evaluates the query instructions against the available source data.
6. The updated output becomes available to the model if the refresh completes successfully.

For example, a monthly sales file may receive 500 additional rows. If the source connection and transformation logic remain valid, Power Query can apply the existing steps to the updated data without requiring you to manually repeat every transformation.

11.2 Why Refresh May Fail

• The source file has moved or been renamed.
• A worksheet or database table has been deleted.
• A required column has been renamed or removed.
• The incoming data contains values incompatible with the expected data type.
• Credentials or source permissions have changed.
• A transformation depends on assumptions that are no longer valid.

12. Query Dependencies and Step Dependencies

Power Query transformations often depend on earlier steps. Queries can also depend on other queries. Understanding these dependencies helps prevent errors when modifying a data preparation workflow.

12.1 Step Dependency Example

Consider the following sequence:

1. Source imports the data.
2. Promoted Headers creates the column names.
3. Changed Type assigns data types to those columns.
4. Removed Columns deletes unnecessary fields.
5. Filtered Rows filters the Region column.

If Region is deleted in Removed Columns, the later Filtered Rows step may fail because it can no longer find that column. The dependency between the steps must be considered when changing the query.

12.2 Query Dependencies

A query may use another query as its source. For example, a staging query may import raw sales data, while a reporting query references the staging query and performs additional transformations.

Changing a staging query can affect other queries that depend on it. Before editing shared queries, review the Query Dependencies view when available and test the downstream outputs.

13. Duplicate Queries, Reference Queries, and Staging Queries

13.1 Duplicate Query

Duplicating a query creates a separate copy of its query definition and current transformation logic. The duplicate can be modified independently, but changes to the original query are not automatically inherited as ongoing source changes to its definition.

Use case: Create separate copies of a dataset for different transformation approaches.

13.2 Reference Query

Referencing a query creates a new query whose starting point is the output of the original query. The reference can reuse upstream preparation logic while adding further transformations.

Use case: Use one cleaned sales dataset to create separate queries for regional sales, monthly summaries, and product analysis.

13.3 Staging Query

A staging query is used to establish a reusable starting point for data preparation. It often handles source access and common cleaning operations before other queries build on the result.

For example, a Sales_Staging query might connect to the source, promote headers, standardize column names, and assign data types. Separate reference queries can then filter the data for different business requirements.

13.4 Comparison

These operations do not necessarily duplicate the physical source data. Their effect on evaluation and performance depends on the source, query structure, and whether transformations can be folded back to the source.

14. Query Load Settings and Enable Load

Not every query needs to appear as a table in the Power BI data model. Some queries exist only to prepare data for other queries.

14.1 Enable Load

The Enable Load setting determines whether a query's output is loaded into the model. Disabling load can be useful for staging or helper queries whose results are consumed by other queries rather than used directly in reports.

14.2 Practical Example

• Sales_Staging: Connects to the source and performs shared cleaning. Its load may be disabled if it is only used as an intermediate query.
• Sales_Final: References Sales_Staging, applies report-specific transformations, and loads the final table.
• Sales_Summary: References the cleaned data and creates an aggregated table if required by the reporting design.

Disabling load does not mean that a query is never evaluated. A query may still be evaluated when another loaded query depends on it.

15. Query Folding and Applied Steps

Query folding occurs when Power Query can translate supported transformation operations into a language understood by the source system, such as SQL for a relational database. The source then performs some of the processing before returning the results.

15.1 Example

Suppose Power BI connects to a SQL database containing ten million sales records, but the report only needs records for the current year. If the filter can be folded, the database may perform the filtering and return fewer rows.

15.2 Why Query Folding Matters

• It can reduce the amount of data transferred to Power BI.
• It can improve refresh performance for supported sources and transformations.
• It can reduce the amount of processing performed locally.
• It can help queries work efficiently with large datasets.

Not all transformations can be folded, and folding support depends on the source connector and the operations used. In Power Query Editor, options such as View Native Query or folding indicators may help inspect folding behavior when supported.

16. Common Errors and Troubleshooting

16.1 A Systematic Troubleshooting Method

1. Open Power Query Editor and select the affected query.
2. Identify the first step that displays an error.
3. Select the preceding step to determine whether the data was valid before the failure.
4. Inspect column names, data types, and values in the preview.
5. Review the formula bar or Advanced Editor if necessary.
6. Correct the transformation or source issue.
7. Check all subsequent steps for additional errors.
8. Apply the changes and refresh the model to verify the solution.

17. Best Practices for Managing Queries and Applied Steps

• Use meaningful query names: Names such as Sales_Staging and Sales_Final communicate the query's purpose.
• Rename important steps: Use descriptions that explain the business operation rather than leaving every step with a generic name.
• Remove unnecessary columns early when practical: This can reduce the amount of data processed, particularly when the operation folds to the source.
• Assign correct data types: Use numeric types for measures, Date for dates, and Text for identifiers that should not be treated as numbers.
• Validate filters: Confirm that the conditions match the business requirement and do not unintentionally exclude valid records.
• Review dependencies before deleting steps: Later transformations may rely on columns created or retained by earlier steps.
• Use reference queries strategically: Reuse shared cleaning logic when several outputs depend on the same prepared data.
• Handle missing and invalid values deliberately: Decide whether to replace, remove, retain, or flag them.
• Test refreshes: Confirm that the query works when new data arrives, not just with the initial sample.
• Document complex transformations: Use descriptive step names and comments in M code where appropriate.
• Protect sensitive information: Avoid embedding passwords, secrets, or confidential values in query scripts.
• Maintain a backup for major changes: Save a version of the PBIX file or maintain appropriate version control before restructuring critical queries.

18. Hands-On Practice Exercise

Exercise: Prepare an Employee Dataset

Create an Excel file containing these columns: EmployeeID, EmployeeName, Department, Salary, JoiningDate, and Remarks.

Tasks

1. Import the Excel file into Power BI Desktop.
2. Open Power Query Editor.
3. Rename the query to Employee_Staging.
4. Promote the first row to column headers if required.
5. Assign correct data types to all columns.
6. Remove the Remarks column.
7. Filter the dataset to retain employees from the IT department.
8. Remove blank rows and investigate duplicate employee IDs.
9. Rename important Applied Steps to make their purpose clear.
10. Create a reference query named IT_Employees.
11. Inspect the Applied Steps in both queries.
12. Load the required final query and validate the output.

Expected Learning Outcome

After completing this exercise, you should be able to create a query, manage its transformation steps, understand query dependencies, and use transformation history to review how the final data was produced.

19. Interview Questions and Answers

Q1. What is a query in Power BI?

Answer: A query defines the instructions used to retrieve data from a source and transform it into a required structure for analysis.

Q2. What are Applied Steps?

Answer: Applied Steps are the sequence of transformation operations displayed in Power Query Editor for a selected query.

Q3. What is transformation history?

Answer: In Power Query, transformation history commonly refers to the ordered list of transformations represented by Applied Steps. It helps users inspect and maintain the data preparation workflow.

Q4. What happens when you delete an Applied Step?

Answer: The selected transformation is removed from the query definition. Later steps may change their output or produce errors if they depend on the deleted step.

Q5. Can Applied Steps be renamed?

Answer: Yes. Steps can generally be renamed through the Applied Steps context menu or the available renaming command.

Q6. What is Power Query M?

Answer: Power Query M is the language used to express data extraction and transformation logic. Power Query Editor generates M expressions for many graphical transformations.

Q7. What is the purpose of Advanced Editor?

Answer: Advanced Editor displays the complete M script for a query and allows users to inspect or edit its transformation logic.

Q8. What is the difference between duplicating and referencing a query?

Answer: Duplicating creates an independent copy of the query definition, while referencing creates a query that starts from another query's output.

Q9. What is query folding?

Answer: Query folding is the process of translating supported Power Query transformations into operations executed by the data source, potentially improving performance.

Q10. How can you identify a transformation error?

Answer: Review the Applied Steps sequentially and identify the first step where the expected output becomes invalid or an error appears.

Q11. Why should column data types be checked?

Answer: Correct data types ensure that dates, numeric values, text, and other fields are interpreted appropriately and reduce conversion and calculation problems.

Q12. Does transformation history provide a permanent audit log?

Answer: No. Applied Steps describe the current transformation logic. A separate version-control or auditing process may be necessary to preserve historical changes.

20. Quick Revision Notes

• A query defines how data is retrieved and transformed.
• Power Query Editor is the main environment for data preparation in Power BI Desktop.
• Applied Steps displays the transformations associated with the selected query.
• Transformation history helps users understand and troubleshoot the sequence of operations.
• The formula bar shows the M expression for a selected step when enabled.
• Advanced Editor displays the full M script.
• Deleting or reordering steps can affect downstream transformations.
• Duplicate and reference queries serve different purposes.
• Enable Load determines whether a query's output is loaded into the model.
• Query folding can allow supported transformations to execute at the source.
• Refresh re-evaluates query instructions against the available source data.
• Descriptive query names, correct data types, and validation improve maintainability.

21. Conclusion

Understanding queries, Applied Steps, and transformation history is essential for preparing reliable data in Power BI. Queries define the overall data preparation process, Applied Steps organize individual transformations, and the ordered transformation sequence helps users understand how the final output was produced.

By learning to edit steps, inspect M code, manage query dependencies, troubleshoot errors, and validate refreshes, Power BI users can create more efficient, reusable, and maintainable data preparation workflows.

Practice these concepts with real datasets to build confidence in Power Query Editor and prepare data effectively for dashboards, reports, and business analysis.

Additional Learning Resources

• Explore Microsoft Power BI Training
• Register for a Course Demo
• Visit JustAcademy