Query Folding Details for Power BI

Hi Sudip_J ,

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

SELECT * FROM Sales WHERE Region = 'West'

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.

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

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 more quickly.