Query Folding And Writing Your Own SQL Queries In Power Query/Power BI/Excel Get & Transform

When you connect to a relational database like SQL Server in Power BI/Power Query/Excel Get & Transform you have two choices about how to get the data you need:

  1. You can choose a table from the database and then either use the Query Editor UI or write some M to get the data you need from that table. For example, you might choose a table that has one row for every product that your company sells and then, using the UI, filter that down to only the products that are red.
  2. You can enter a SQL query that gets the data you need.

Something that you might not realise is that if you choose the second option and then subsequently use the UI to apply even more filtering or transformation, then those subsequent steps will not be able to make use of query folding.

As an example of option (1), imagine you connect to the DimProduct table in the SQL Server Adventure Works DW database like so:



The following M query is generated by the Query Editor when you filter the table to only return the red products and remove all columns except EnglishProductName. That’s very easy to do so I won’t describe it, but here’s the M:

    Source = 
    #"Adventure Works DW" = 
		[Name="Adventure Works DW"]
    dbo_DimProduct = 
	#"Adventure Works DW"{
    #"Filtered Rows" = 
		each ([Color] = "Red")
    #"Removed Other Columns" = 
		#"Filtered Rows",
    #"Removed Other Columns"


Using the View Native Query option, you can find out that the following SQL is generated to get this data:

select [_].[EnglishProductName]
from [dbo].[DimProduct] as [_]
where [_].[Color] = 'Red'



It’s pretty clear that query folding is taking place for the filter on “red” and for the selection of the required column.

However, if you enter the following SQL query when you first connect to the database:

select * from dimproduct


And then, after that, filter the table and remove columns in exactly the same way, you get the following M query:

    Source = 
		"Adventure Works DW", 
		[Query="select * from dimproduct"]),
    #"Filtered Rows" = 
		each ([Color] = "Red")),
    #"Removed Other Columns" = 
		#"Filtered Rows",
    #"Removed Other Columns"

If you now try to use the View Native Query option on either the Removed Other Columns or Filtered Rows steps you’ll find it’s greyed out, indicating query folding is not taking place for those steps:


The query you enter is run and then Power BI applies the filter and selects the column itself in the resultset that the SQL query returns.

This obviously has big implications for performance. The lesson here is that if you’re going to write your own SQL query in the Query Editor, you should make sure it does all of the expensive filters and transformations you need because anything else you do in the query will happen outside the database in Power BI or Excel.

10 responses

  1. Pingback: Dew Drop - June 12, 2017 (#2498) - Morning Dew

  2. Pingback: Data Privacy Settings In Power BI/Power Query, Part 3: The Formula.Firewall Error – Chris Webb's BI Blog

  3. Pingback: Using Your Own SQL Queries For Tables With Modern Data Sources In SSAS 2016+ And Azure Analysis Services – Chris Webb's BI Blog

  4. Hi Chris,

    Could I please ask you to have a look at the issues reported in the threads below? It’s a very interesting issue that I think has to do with query folding vs Power BI native query. Once you get to very large source tables that are accessed via a SQL query, and you have a subsequent merge in Power BI (in my case the merge was with an excel file), every refresh gives different results.

    Power BI seems to issue the query twice and then combines the results. The problem arises if there is no explicit sort in the original query. Power BI assumes an explicit sort exists, and simply combines the first x results returned from QuerySplit1 with the remaining total-x results from QuerySplit2

    As a result, some records are dropped, and others duplicated at random.

    Here is the thread that I created:
    In my report, I linked to other cases where the issue was reported. Subsequent to initially reporting it, I found a way to bypass the problem (as did many other people subsequently), but we all feel that this is an issue that will erode trust in Power BI due to the fact that the query issued is not behaving as expected, and that incorrect results are returned without any error.

    Recently there has been a number of people adding to this thread with the same issue.

    We all would really appreciate your help in bringing this to the attention of someone at Microsoft. I would be very happy to discuss this in more detail if you need further info to replicate

    Thanks very much

    Ina Nortje

  5. Pingback: That’s Not Normal! The Frustrations of Dirty Data and Power BI Features to Help You Transform Your Data Sets - Aptude IT Solutions

  6. Has anyone had a problem with rounding when dividing columns from SQL database?
    For some reason you get only 6 decimals in result if you want to fold it.
    convert(decimal(38,6), [number])
    If you use Value.Divide, then you get more decimals, bud it doesn’t fold.

    This is my sample https://imgur.com/a/Vq48E6Y

  7. @Milang I had exactly this problem today. There was a Power BI model which was converting between two currencies and one of the exchange rate had 22 decimal points. The calculations were done inside the query editor and because of query folding only 6 decimal places were used resulting in incorrect currency conversion. Since the data was being pulled from a SQL database I wrote a native query to solve the problem to give the correct currency conversion.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: