Error: This step results in a query that is not supported in DirectQuery mode.

[HamidBee]

I have the following query that I used to connect to a data source in SQL:

SELECT OID.OrderID,O.OrderDate,UnitPrice*Quantity AS Total 
FROM Orders As O
FULL OUTER JOIN [Order Details] AS OID
ON O.OrderID = OID.OrderID

I used the DirectQuery method. I then tried to perform a filter between two date ranges. However, when I did this I received an error reading:

"This step results in a query that is not supported in DirectQuery mode."

Here is the M query:

let 
    Source = Sql.Database("(localdb)\Local", "Northwind", [Query="SELECT OID.OrderID,O.OrderDate,UnitPrice*Quantity AS Total#(lf)FROM Orders As O#(lf)INNER JOIN [Order Details] AS OID#(lf)ON O.OrderID = OID.OrderID"]),
    #"Filtered Rows" = Table.SelectRows(Source, each [OrderDate] > #datetime(1996, 1, 1, 0, 0, 0))
in
    #"Filtered Rows"

I'm not sure why this is happening and how to fix it. Any help would be greatly appreciated. 

[HamidBee · Accepted solution]

I was able to fix the following by firstly using Import mode instead of DirectQuery. Here are the full steps I followed:

No I need to use the EnableFolding=true command and this is only available when using the Value.NativeQuery function. I created a new step and typed the following:

= Value.NativeQuery( Source, "
SELECT OID.OrderID,O.OrderDate,UnitPrice*Quantity AS Total 
FROM Orders As O
FULL OUTER JOIN [Order Details] AS OID
ON O.OrderID = OID.OrderID", null, [EnableFolding=true])

Now if I apply any parameter based filters to the dataset it will work without any errors.

[artemus]

I think you forgot the EnableFolding=true in the Sql.Database 3rd parameter. This is needed if you want folding to work with SQL when using a custom query.