Imagine you’re working in Power BI and come across a dataset where first and last names are stored in a single column—like “JohnDoe” instead of the more readable “John Doe”. This can make sorting, filtering, and analysis more challenging, especially when you need to organize names separately.

Luckily, Power BI’s Query Folding feature allows you to easily break apart these merged names based on specific text patterns. Query folding is a process in Power Query where transformations applied to your data are translated into a single query statement that can be executed by the data source. This helps optimize performance by offloading as much of the query processing as possible to the data source. Whether your data is formatted as “JohnDoe”, “John.Doe”, or “John-Doe”, you can use this tool to separate first and last names efficiently.

Query folding in Power Query Editor enhances the performance of your Power BI reports by optimizing how transformations are processed. As you apply changes in Power Query, these transformations are automatically translated into native SQL queries, such as simple

This blog will walk you through how to split columns using split a column while keeping the original intact. By the end, you’ll have a cleaner dataset that’s easier to work with. Check out this blog for an introductory lesson on getting started with Power BI.

Use Cases for query folding:

Filtering

Query folding in Power BI allows filtering operations to be pushed back to the data source, making queries more efficient. When applying filters in Power Query, such as filtering a Date column to show only the current year, Power BI translates this into a SQL WHERE clause (if the source supports it). This reduces the amount of data retrieved, improving performance. To ensure query folding, apply filters as early as possible in Power Query and check the View Native Query option to confirm that Power BI is offloading the operation to the source.

Merging Data from Multiple Tables from the Same Data Source

When merging tables within Power BI, query folding can help optimize performance by ensuring that the join is executed at the data source rather than within Power BI. For example, if you are combining Sales Orders and Customer Information from the same SQL database, Power BI will generate a SQL JOIN rather than fetching both tables separately and merging them locally. To maintain query folding, ensure both tables originate from the same data source and that transformations applied before merging do not break folding.

Renaming Columns

Renaming columns is a simple transformation that often supports query folding, meaning the renaming operation is performed at the source instead of within Power BI. For instance, if you rename a column from “Cust_ID” to “Customer_ID”, Power BI translates this into an AS statement in SQL (SELECT Cust_ID AS Customer_ID). To preserve query folding, apply renaming directly in Power Query before any transformations that might break folding, such as custom functions or manually entered data.

Removing Columns

Removing unnecessary columns is a great way to improve query performance, and when query folding is maintained, this operation ensures that Power BI doesn’t retrieve unneeded data from the source. For example, if a Customer Table has 30 columns but you only need Customer ID, Name, and Email, using Remove Columns in Power Query pushes this operation to the source as a SELECT statement with only the required fields. This reduces data transfer time and improves report efficiency. To check if query folding is still happening, right-click a step in Power Query and select View Native Query to see if the removal is reflected in the SQL query.

When to Use Power Query vs. DAX for Transformations

When transforming data in Power BI, you have two main options:

1. Power Query Editor (Transform Data)
2. DAX Editor (Calculated Columns & Measures)

While Power Query is great for shaping and cleaning data before it loads into the model, some transformations—like splitting a column—can be more efficient when done using DAX instead.

Example: Splitting a ‘Full Name’ Column into First and Last Name

*Watch this YouTube Video on how to split a column, or read below for a step by step guide.*

Suppose you have a Full Name column in your dataset (e.g., “John Doe”), and you need to split it into separate First Name and Last Name columns.

Power Query Method (Less Efficient)

You would need to:

• Duplicate the column
• Use the Split Column feature (based on space as a delimiter)
• Rename the new columns
• Ensure the changes don’t break query folding
• Load the transformed data into Power BI

Downside: Each step modifies the data before loading it, increasing refresh times and affecting performance, especially with large datasets.

DAX Method (More Efficient)

Instead, you can create two calculated columns directly in the DAX editor without modifying the source data:

1. Locate the column to split

• Identify the column containing the full names that need to be separated (e.g., “Full Name”).

2. Create a new column for “First Name”

• In Data View, click New Column to add a calculated column.

3. Enter the DAX formula for “First Name”

• Use the following formula to extract the first name:

First Name = LEFT( ‘Table'[Full Name], FIND(” “, ‘Table'[Full Name]) – 1 )

• This extracts all characters before the first space.

4. Create a new column for “Last Name”

• Click New Column again to add another calculated column.

5. Enter the DAX formula for “Last Name”

• Use the following formula to extract the last name:

Last Name = RIGHT( ‘Table'[Full Name], LEN(‘Table'[Full Name]) – FIND(” “, ‘Table'[Full Name]) )

• This extracts everything after the first space.

6. Verify the split columns

• Check the Fields pane to confirm that “First Name” and “Last Name” columns have been added successfully.
• Add these columns to a Table visualization to see the results.

Why This is Better?

No additional query processing – The split happens dynamically in Power BI, not before the data loads.

Faster performance – DAX operates at the model level, making calculations more efficient for large datasets.

Easier maintenance – No need to worry about breaking query folding or reloading transformed data.

For a more in-depth analysis of Query Folding, check out the Microsoft Page.

Leveraging Power BI’s Query Folding feature can significantly enhance the performance and efficiency of your data processing tasks. By understanding and utilizing this feature, you can ensure that your Power BI reports are optimized for faster data refreshes and smoother performance. Whether you choose to use Power Query or DAX for your transformations, each method has its own advantages and can be selected based on your specific needs.