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. 

[rnola16]

Can this query be not performed in Direct Query Mode. ? I'm in the same situation where I using a SQL to build the query, used Direct Query to set the table first and trying to create dynamic parameters on the table for users to input. Is this not possible in Direct query. ? Because using Import Query will be hard to bring in billions of records. Any help is appreciated. 

Using: On Premise db Teradata

BI application in Cloud.

[Anonymous]

Hi @HamidBee ,

If the Power Query Editor query is overly complex, an error occurs. To remedy the error, either delete the problematic step in Power Query Editor, or import the data instead of using DirectQuery. 

Don't use the relative data filtering in Power Query Editor. It's possible to define relative date filtering in Power Query Editor. For example, to filter to the rows where the date is in the last 14 days.

Please check the following document for limitations of Direct Query mode and workarounds for date filtering:

Using DirectQuery in Power BI - Power BI | Microsoft Learn

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[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.