Query Folding Details for Power BI

[Sudip_J]

Hi Teams,
I want to see entire process of query folding which connect from MS SQL server data sources.I have list of query which arte in  following -
(i) How to connect Power Query transformation process in SQL Server or MYSQL 
(ii) How transformation process in power query goes to directly at native data source
(iii) How it push back to the native data source 
(iv) How it lighten up the power query for dashboard optimization 
@all 
Thanks and Regards,
Sudeep Kumar

[Ray_Minds]

Hi @Sudip_J

1. How to connect Power Query transformation process in SQL Server or MYSQL 
Solution :

• Go to Home > Get Data > SQL Server 

  

  • Enter server name and database, then choose Import or DirectQuery.

    

    • Select the required table and click on the Transform Data to open Power Query Editor.
      • Power Query creates a navigation step that defines the initial data source connection.

        Close and Apply to save the changes 

        

        (ii) How Power Query Transformations Are Sent to the Native Source (Query Folding)

        • Connect the SQL Server 
        • Expand the Advance Option
        • Past the SQL Statement in the SQL Statement Box and hit on the Ok

          

          Or past the SQL query within double quote (" SQL Query ") like below 

          

          (iii) How It Pushes Back to Native Data Source

          Behind the scenes:

          • Power Query builds an M script.
          • Power BI translates M steps into T-SQL or MySQL dialect.
          • The final folded SQL is executed on the server, returning only necessary data.
            SELECT 
                                CAST([ProductID] AS INT) AS ProductID,
                                [Name],
                                [ProductNumber],
                                [MakeFlag],
                                [FinishedGoodsFlag],
                                [Color],
                                [SafetyStockLevel],
                                [ReorderPoint],
                                [StandardCost],
                                [ListPrice],
                                [Size],
                                [SizeUnitMeasureCode],
                                [WeightUnitMeasureCode],
                                [Weight],
                                [DaysToManufacture],
                                [ProductLine],
                                [Class],
                                [Style],
                                [ProductSubcategoryID],
                                [ProductModelID],
                                [SellStartDate],
                                [SellEndDate],
                                [DiscontinuedDate],
                                [rowguid],
                                [ModifiedDate]
                            FROM [Production].[Product]
                            WHERE [Color] IS NOT NULL
            (iv) How it lighten up the power query for dashboard optimization 
          • Remove the un-necessary columns which we are not using in the report
          • Change the data types based on the columns holding the types of data
          • Push the filters and joins back to the SQL Server 
          • Avoid the same transformation  using multiples times 
          • Disable the table load from Power Query if we are not using in the Modeling or even reporting 
          • avoid using Calculated column use power Query custom or conditional column if required 
          • use the Measures for aggregation  
            
            

            Best regards,
            Ray Minds
            http://www.rayminds.com
            https://www.linkedin.com/company/rayminds/

            If this post helps, then please consider Accept it as the solution to help the other members find it...
            
            

[Sudip_J]

Hi burakkaragoz ,

Thanks for answering this question but i want to see the exact process so that next time i can do myself .
Can you please attached any links so that i can follow the steps and get know exactly ??
Thanks and Regards 
Sudeep Kumar

[v-sshirivolu · Accepted solution]

Hi @Sudip_J ,
Thanks for reaching out to the Microsoft fabric community forum.

Here are the links to assist you in understanding and implementing query folding in Power BI when connecting to SQL Server and MySQL data sources:

1. Query Folding Basics 

• https://learn.microsoft.com/en-us/power-query/query-folding-basics

• Provides an overview of query folding, its benefits, and how Power Query translates steps into native queries.

2. Power Query M Language Specification

• https://learn.microsoft.com/en-us/powerquery-m/power-query-m-language-specification

• Offers detailed information on the M language used in Power Query for data transformations.

3. Connect to MySQL Database from Power Query Desktop

• https://learn.microsoft.com/en-us/power-query/connectors/mysql-database

• Step-by-step guide on connecting Power BI to a MySQL database using Power Query.(learn.microsoft.com)

4. Connect to SQL Server Database from Power Query Desktop

• https://learn.microsoft.com/en-us/power-query/connectors/sql-server

• Instructions on establishing a connection between Power BI and SQL Server databases.

5. Power Query Documentation Hub

• https://learn.microsoft.com/en-us/power-query/

• Central hub for all Power Query documentation, including connectors, transformations, and performance optimization.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Sreeteja.
Community Support Team 

 

[v-sshirivolu]

Hi @Sudip_J ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution and give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

[v-sshirivolu]

Hi @Sudip_J  ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solutionand give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

[v-sshirivolu]

Hi @Sudip_J ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution and give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

[Elena_Kalina]

Hi @Sudip_J 

Query folding is a critical optimization technique in Power BI that pushes data transformation logic back to the source database rather than processing it locally. Here's a detailed explanation of your queries:

(i) Connecting Power Query to SQL Server/MySQL and Transformation Process

1. Connection Setup:

   • In Power BI Desktop, select "Get Data" → "SQL Server" or "MySQL"

   • Enter server name, database, and credentials

   • Power Query establishes a connection using appropriate drivers

2. Initial Query:

   • When you select a table, Power Query generates a basic M query like:

     = Sql.Database("server", "database", [Query="SELECT * FROM Sales"])

   • Or for MySQL:

     = MySQL.Database("server", "database", [Query="SELECT * FROM Sales"])

(ii) Transformation Process at Native Data Source

Query folding occurs when Power BI can translate your Power Query (M) transformations into native SQL queries:

1. Supported Operations that typically fold:

   • Filtering rows (Table.SelectRows)

   • Selecting columns (Table.SelectColumns)

   • Sorting (Table.Sort)

   • Grouping (Table.Group)

   • Joins (Table.Join)

   • Basic calculations (simple arithmetic in Table.AddColumn)

2. Example:

   = Table.SelectRows(
       Table.SelectColumns(Sales, {"OrderID", "Amount"}),
       each [Amount] > 1000
     )

   Folds to SQL:

   SELECT OrderID, Amount FROM Sales WHERE Amount > 1000

(iii) How Transformations Push Back to Native Source

1. Verification Process:

   • Right-click a step in Power Query Editor → Check "View Native Query"

   • If enabled, the step is folded; if grayed out, folding was broken

2. Common Reasons Folding Breaks:

   • Using non-foldable functions (e.g., Text.Combine)

   • Complex custom columns

   • Certain data type conversions

   • Merges/joins with incompatible data types

   • Using Table.Buffer or other caching functions

3. Optimization Tips:

   • Apply filters early in the query

   • Perform column selection before complex operations

   • Use SQL views or stored procedures for complex logic

(iv) Optimizing Dashboard Performance

Query folding improves performance by:

1. Reducing Data Transfer:

   • Only the final result set is transferred to Power BI

   • Avoids bringing entire tables for local processing

2. Leveraging Source Engine:

   • Utilizes SQL Server's optimized query processor

   • Benefits from database indexes and statistics

3. Monitoring Techniques:

   • Use Performance Analyzer in Power BI Desktop

   • Check SQL Server Profiler to see actual queries executed

   • Review query execution plans in SQL Server

4. Advanced Techniques:

   • Use "Import" mode with query folding for scheduled refreshes

   • Consider DirectQuery for very large datasets (keeps all processing on server)

   • Implement incremental refresh with folded filters

To maximize query folding benefits, structure your transformations to keep as much logic as possible in foldable operations before applying any non-foldable steps.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!  Thank you.

[burakkaragoz]

Hi @Sudip_J ,

Great set of questions. Query folding is a key concept in Power BI performance tuning, especially when working with large datasets from SQL Server or MySQL. Let me walk you through each point with some examples.

(i) How to connect Power Query transformation process in SQL Server or MySQL

You can connect to SQL Server using:

Home > Get Data > SQL Server

Once connected, Power Query Editor lets you apply transformations like filtering, grouping, merging, etc.

(ii) How transformation process in Power Query goes to directly at native data source

This is where query folding comes in. When you apply transformations in Power Query (like filtering rows or renaming columns), Power BI tries to translate those steps into SQL and push them back to the source.

For example:

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

gets translated to:

SELECT * FROM Sales WHERE Region = 'West'

You can check if folding is happening by right-clicking a step in Power Query and selecting "View Native Query". If it's grayed out, folding has stopped at that step.

(iii) How it push back to the native data source

As long as the transformations are foldable, Power BI sends a single SQL query to the source when loading data. This reduces memory usage and speeds up refresh times.

(iv) How it lighten up the power query for dashboard optimization

By folding as much logic as possible to the source, you avoid loading unnecessary data into Power BI. This keeps your model lean and improves performance.

Tips:

• Avoid using custom columns with complex M logic early in the steps
• Keep foldable steps at the top of the query
• Use SQL views if you need more control over folding

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI