Blog

You are currently viewing Query folding in PowerBI

Query folding in PowerBI

Loading

In this blog you will learn about the various data source connections and the Query folding in PowerBI.

Understanding Data Source connections

In Power BI, connecting to data sources is the foundational step for any data analysis project. Power BI supports a diverse range of data sources, including files (such as Excel and CSV), databases (such as SQL Server and Oracle), and online services (such as SharePoint and Dynamics 365).

Steps to connect to a data source

Step 1. Open Power BI Desktop.

Step 2. Go to the Home tab and click the Get Data button.

Step 3. Select the type of data source you want to connect to. For example:

  • If your data stored file (csv, excel etc.); Go to the file location and select the file you want to import.
  • If your data stored in database; fill the required details (username, password, server name, database name etc.) to access. And select the table that you want to use (single/multiple).

Step 4. Click Load or Transform Data (as requirements)

Query Folding and its impact on performance

What is query folding in PowerBI?

Query folding is an important concept in Power BI, which refers to the process where transformations you define in Power Query are translated back into the native query language (such as SQL) of the data source. This means that the heavy work of data processing is done by the data source, making your Power BI reports faster and more efficient.

Benefits of query folding

  • Data reduction: Query folding reduces the amount of data that is transferred from the source to Power BI by performing filtering and aggregation operations on the source.
  • Source system processing: It leverages the processing power of the data source for complex transformations, resulting in faster query execution.
  • Efficient refresh: Data refresh operations become more efficient as only the required data changes are fetched from the source, reducing the load on Power BI.
  • Handling large datasets: By offloading processing to the data source, Power BI can handle large datasets, improving overall scalability and performance.

Writing queries to take advantages of query folding

For SQL Server:

Step 1. Open Advanced Editor

Step 2. Write following query to fetch data: 

let
    Source = Sql.Database("ServerName", "DatabaseName", [Query="SELECT * FROM TableName;"])
in
    Source

Avoidable transformation that can break query folding:

  • Custom M functions
  • Text Manipulation
  • Data Type Conversion

Some common transformations:

  1. Filter Rows: It reduce volume of data.


Example: Suppose you have a data source, and you want to filter rows where column 1 is equal to “value”. 

let
    Source = YourDataSource,
    FilteredData = Table.SelectRows(Source, each [Column1] = "Value")
in
    FilteredData

  1. Remove Unnecessary Columns: Remove nos. of column that is not useful for certain project.


Example: If your data has columns UnnecessaryColumn1 and UnnecessaryColumn2 that you don’t need, you can delete them.

let
    Source = YourDataSource,
    ReducedColumns = Table.RemoveColumns(Source, {"UnnecessaryColumn1", "UnnecessaryColumn2"})
in
    ReducedColumns

  1. Sort and Aggregate: It helps to optimize performance.


Example: Suppose you need to sort the data according to column3 in ascending order and then group the data according to KeyColumn to sum the second column (ColumnToAggregate).

let
    Source = YourDataSource,
    SortedData = Table.Sort(Source, {{"Column3", Order.Ascending}}),
    GroupedData = Table.Group(SortedData, {"KeyColumn"}, {{"AggregatedColumn", each List.Sum([ColumnToAggregate]), type number}})
in
    GroupedData

Step 3. In the Power Query Editor, right-click on each step in the Applied Steps pane.

Check if View Native Query is available. If it is, query folding is happening for that step.

Step 4. click Close & Apply

Conclusion

Understanding and effectively using data source connections, query folding, and optimized query writing in Power BI is essential to building efficient, high-performing reports and dashboards. By connecting to the proper data sources and leveraging the power of query folding, you can significantly reduce data load and processing times, leading to faster and more responsive analytics.

If you like the article and would like to support me, make sure to:

Leave a Reply