Query folding is the process Power Query uses to create a SQL statement and sends it to the server. So as an example, I imported the Purchasing table from the Microsoft World Wide Importders sample database on SQL Server using basic transformations in Power Query - removing columns, adding a conditional column. the M code is:
let
Source = Sql.Databases("localhost"),
WideWorldImportersDW = Source{[Name="WideWorldImportersDW"]}[Data],
Dimension_Customer = WideWorldImportersDW{[Schema="Dimension",Item="Customer"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Dimension_Customer,{"Customer Key", "WWI Customer ID", "Customer", "Bill To Customer", "Category", "Buying Group", "Primary Contact", "Postal Code", "Valid From", "Valid To"}),
#"Added Purchasing Power" = Table.AddColumn(#"Removed Other Columns", "Purchasing Power", each if [Buying Group] = "N/A" then "Standard"
else if [Buying Group] = "Tailspin Toys" then "Standard"
else "Discount", type text)
in
#"Added Purchasing Power"
Power Query though "folds" all of that. It doesn't acutally do any of that in the PQ mashup engine. It analyzes it, and send the following SQL statement to the server:
select [_].[Customer Key] as [Customer Key],
[_].[WWI Customer ID] as [WWI Customer ID],
[_].[Customer] as [Customer],
[_].[Bill To Customer] as [Bill To Customer],
[_].[Category] as [Category],
[_].[Buying Group] as [Buying Group],
[_].[Primary Contact] as [Primary Contact],
[_].[Postal Code] as [Postal Code],
[_].[Valid From] as [Valid From],
[_].[Valid To] as [Valid To],
case
when [_].[Buying Group] = 'N/A'
then 'Standard'
when [_].[Buying Group] = 'Tailspin Toys'
then 'Standard'
else 'Discount'
end as [Purchasing Power]
from
(
select [Customer Key],
[WWI Customer ID],
[Customer],
[Bill To Customer],
[Category],
[Buying Group],
[Primary Contact],
[Postal Code],
[Valid From],
[Valid To]
from [Dimension].[Customer] as [$Table]
) as [_]
The server does all of the work and simply sends back the relevant data. You can do pivots, grouping, merges, appends, etc. Tons of stuff will fold.
For Direct Query to work, Query Folding must be allowed to happen. Direct Query sends SQL statements to the server and returns immediately those results. There is no other work done or allowed in the Power Query mashup engine. SQL to SQL statements, that is it. As soon as you "break folding" Direct Query breaks. Import Queryies will still work. They will do folding to the point the folding breaks, then do the rest internally in the gateway/mashup engine. Direct Query though doesn't allow it. 100% folding, or you have to switch to Import for that query.
I don't have access to a Postgre database to test with, but I would assume in the M code I have posted above for SQL Server, the PostGre connection would have its own code, and you'd need to modify it to enable query folding per that article link. I'm not sure why - SQL Server it just works - there is no flag to set. But there must be something with Postgre that it need to be able to be turned off and on as needed.
And you would need to add it to each query to your server. It is not a global setting but a per-query setting.
Did I answer your question? Mark my post as a solution!Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
