Query Folding in Power BI: Key Concepts Explained

Query folding in Power BI is a feature that helps users to efficiently run transformations on a huge volume of data. To get the most out of query folding, you need to understand what it is, and how it works.

In this guide, you’ll discover what query folding in Power BI is, understand how it works, and why it is important.

Table of Contents:

1. What is Query Folding?
2. Understanding Power BI Query Folding
3. Why is Query Collapsing in Power BI Important?
4. How to Enable Query Folding in Power BI?
5. What are the Steps to Set Up Query Folding?
6. How to Create Visualizations in Power BI?
7. Wrap Up

First…

What is Query Folding?

Query Folding is the feature that enables the Power Query to transform and execute large volumes of data from the data source. With query folding, Power Query can efficiently handle vast volumes of data.

Understanding Power BI Query Folding

Query folding is synonymous with writing down detailed instructions in SQL code, and running it through the database for enforcement. Simply put, the heavy lifting is done by the data source, and that will, in turn, reduce the volume of data that is transferred and processed by the Power BI.

Before Power Query executes the query folding, the following conditions have to be met:

1. The data source should be able to accept a query request.
2. The configuration steps must be query-folding friendly. If a single action cannot be folded, you cannot proceed with query folding the entire query.
3. Don’t start the query with your SQL code. Starting the query with your SQL code will make it impossible to fold the query in the steps.

Why is Query Collapsing in Power BI Important?

Here’s the overview of how query folding is important in Power BI.

• Enhanced Performance

With query folding, data transformation tasks will be delegated to the data source. That is, operations like aggregating, joining data, and filtering will be performed by the server – and that’s a more efficient and powerful way of processing data.

Since queries are processed by the data source, only needed data is transferred to the Power BI – and that minimizes the volume of data that is moved to the network. This will, in turn, speed up data retrieval and reduce latency.

• Scalability and Consistency

Since the heavy lifting is handled by the data source, it becomes easy to work with complex and large datasets. Furthermore, the risk of discrepancies is reduced since the operations are handled within the data source.

• Resource Management

Query folding helps in optimizing data workflows. Since data transformations are performed at the data source level, local resources are freed up – and that gives room for more efficient data visualization and modeling tasks.

How to Enable Query Folding in Power BI?

Here are steps to help you enable query folding in Power BI.

1. Right-click on the Report View, and select “Edit Query.” After that, the “Power Query Editor” will be opened.

2. Several transformations will be displayed on the right. Right-click on your desired option, and choose “View Native Query.”

3. From the dialogue box below, you’ll notice that the Query Folding has been performed for the column.

There are cases where the “View Native Query” option is grayed out – and that shows that query fold cannot be applied in the operation.

• Query Folding Indicators

Query folding indicators use an underlying query plan to determine if a query will fold or not. Here is the list of the various indicator icons and their meaning.

• Folding

The folding indicator shows that the query will be executed by the data source up to this step.

• Not Folding

This indicator shows that certain parts of the query will not be processed by the data source. You need to rearrange the query to enable the data source to execute as many transformations as possible.

• Might Fold

The indicators show that the folding status will be determined during the runtime. It cannot be determined beforehand. Might Fold only appears when you’re using ODBC connections or OData.

• Opaque

This indicator shows that the Power BI connector does not support the query plan indicator and tool. As a result, the query plan remains indeterminate.

• Unknown

This indicator shows that the query plan is absent. It could be due to an error. It could also happen when you try running the query plan evaluation, or when you try running something that’s not the query table.

What are the Steps to Set Up Query Folding?

Here are easy steps to help you set up a query folding.

• Connect Power BI to a data source

The first step is to connect to a data source that supports query folding. In this illustration, the Power BI will be connected to the Microsoft Access Database. Connecting to the data source is easy. You’ll have to click on “Get Data” and choose your preferred data source.

Edits are made in the “Query Edit.” To go to the “Query Editor,” click on “Transform Data.”

After opening the “Query Editor,” perform transformations. And that can be done by pushing them into a data source. Transformations could be sorting rows of a column in ascending order.

There is also the option of performing complex transformations like adding a conditional column. To do that, navigate to “Add column,” and click the “Conditional column” icon.

To load your data onto the Power BI, search for the “Close & Apply” button on the home tab and click on it. Repeat the process for all the columns that require query folding.

How to Create Visualizations in Power BI?

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Total Cost	Company Type	Company Name	Expertise Categories	Expertise	Cost
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Plumbing & Heating	15456
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Mechanical Work	10159
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Plumbing & Heating	18045
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Mechanical Work	12695
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Plumbing & Heating	14589
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Welding	11456
Total Cost	Supplier	Power-up Builders	Raw Material	Cement	20561
Total Cost	Supplier	Power-up Builders	Raw Material	Steel	32456
Total Cost	Supplier	Five-star Construction	Raw Material	Bricks	10253
Total Cost	Supplier	Five-star Construction	Raw Material	Timber	9000

Paste the data table onto the “Power Query” window. After that, choose “Create a dataset only” from the options.

Take a look at the left-side menu, and click on “Data Hub.” If there is no data set, you’ll get an error message. However, if there is a data set, Power BI will populate it.

Select the data set that will be used in creating the Sankey diagram. After that, an image similar to the one below will be displayed.

From the dropdown menu, click “Create a report.” After that, choose “Start from scratch.”

The report will be similar to the one below.

Stage 3: Add the Power BI Sankey Diagram Extension by ChartExpo

To get started, you’ll have to use the Power BI visual from AppSource or get the add-in. After that, you’ll have to follow the steps outlined below:

• Open the Power BI visualization panel. The panel is found on the right side of your Power BI dashboard.
• Click on the ellipsis symbol (…) to import the Power BI Sankey diagram extension by ChartExpo.

Click “Get more visuals.”

Using the search box, input “Sankey Diagram for Power BI by ChartExpo.”

Click on the “Add” button.

The “Sankey Diagram for Power BI by ChartExpo” icon will be added to the visualization panel.

Stage 4: Drawing the Sankey Diagram with ChartExpo’s Power BI extension

• From the visualization panel, choose “Sankey Diagram for Power BI by ChartExpo.” After that, a window similar to the one below opens.

There are ways of resizing the visual. Moving on, you’ll have to navigate to the right side of the Power BI dashboard, and look out for “Fields” found next to “Visualizations.”

Choose the fields that will be used in your Sankey Chart. And you have to select the fields using the sequence below.

• Total Cost
• Company Type
• Company Name
• Expertise Categories
• Expertise
• Cost

You’ll have to provide your email address or ChartExpo license key.

Stage 5: Apply a Subscription Key or Activate the ChartExpo Trial.

Choose the ChartExpo visual, and look out for the three icons below the “Build Visual” option.

Select the “Format visual” option. After that, visual properties similar to the one below will be displayed.

If you’re a new user, you can begin using ChartExpo by:

• Entering your email address under the “Trial Mode” section. Toggle the “Enable Trial” icon to activate the 7-day trial.

Since you’re using the 7-day trial, you’ll get a ChartExpo watermark on all Sankey Diagrams you create.

If you have a license key, you should:

• Provide the license key in the “ChartExpo License Key” textbox under the “License Settings.”
• There’s a toggle switch next to “Enable License,” slide it “On.”

If done properly, the Sankey diagram will not come with a watermark.

You can add the prefix (like the $ sign) to the chart. After that, expand the “Stats” properties to include the Prefix value.

To add colors, expand the “Level Colors” properties, and choose the colors.

The changes will be automatically saved.

Insights

Here are three insights you can draw from the chart.

1. The procurement cost is $155k at level 1 (Total Cost).
2. At level 2 (Company Type), 46.7% ($72.3k out of $155k) was allocated to suppliers, while 53.3% ($82.4k) was spent on subcontractors.
3. At level 3 (Company Name), the $72.3k supplier cost was divided into two companies – Five-star Construction and Power-up Builder, with charges of $19.3k and $53.0k respectively.

FAQs

What is the use of query folding in Power BI?

With query folding in Power BI, data transformation is pushed to the source. This reduces data transfer, takes advantage of the source processing power, and optimizes performance.

Does query folding improve performance?

When data transformation is pushed to the data source, it improves performance and reduces data transfer.

How to disable query folding in Power BI?

The “Table.Buffer” function is used in disabling query folding in Power BI.

Wrap Up

Query folding (or query collapsing) helps to improve the efficiency, performance, and resource management of Power BI. Since data is processed in the data source, only relevant data is transferred to the Power BI – and that minimizes the volume of data that is moved over the network.

Query folding helps in the optimization of the data workflows. Since transformations are performed at the data source level, local resources are freed up – and that allows for more efficient data visualization and modeling tasks.

To get the most out of query folding, the following conditions must be fulfilled:

• The data source must accept a query request
• All the configuration steps must allow query folding
• The query should not start with your SQL code.

Now you know what query folding in Power BI is, what transformations and data operations will be performed using the feature?