Forum Discussion
Helper IQuery Folding Details for Power BI
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
-
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-
Solution SupplierHi 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/
- Power Query creates a navigation step that defines the initial data source connection.
- Select the required table and click on the Transform Data to open Power Query Editor.
- Enter server name and database, then choose Import or DirectQuery.