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

[Analmd]

Hi All,

I have connected to a Power BI semantic model (Model A) using DirectQuery mode. Additionally, I am using the Import mode to connect to another dataset (Dataset B) and performing various transformations, such as applying filters. These transformations are working as expected. However, when I attempt to use the function to add a column:

Date.EndOfWeek([deliverydate](__CITATION__1),1)

, I encounter the following error: "This step results in a query that is not supported in DirectQuery mode."

To investigate further, I altered the sequence in which I connected the datasets. By importing Dataset B first via Import mode and then connecting to Model A using DirectQuery mode afterward, everything worked correctly.

Does anyone have any insights into why this might be happening? Your help would be greatly appreciated!

[MasonMA · Accepted solution]

hello @Analmd 

In your second approach, it looks Power BI treats the Import table as a local source and allows more flexible transformations. Then when you connect to Model A via DirectQuery, Power BI can isolate the DirectQuery logic and avoid unsupported transformations.

If you need something like Date.EndOfWeek, do it via DAX calculated column in the model instead of Power Query.

EndOfWeek = ENDOFWEEK('Table'[deliverydate], SUNDAY)

[Shahid12523 · Accepted solution]

You're seeing that error because Date.EndOfWeek is an M function, and DirectQuery mode can't translate it into SQL. When you connect to the DirectQuery source first, Power BI enforces stricter rules. But if you connect the Import source first, it relaxes those rules slightly.

Quick fix: Use DAX instead of M:
EndOfWeek = [deliverydate] + (7 - WEEKDAY([deliverydate], 2))

Or preprocess the column in the source system.

[MasonMA · Accepted solution]

hello @Analmd 

In your second approach, it looks Power BI treats the Import table as a local source and allows more flexible transformations. Then when you connect to Model A via DirectQuery, Power BI can isolate the DirectQuery logic and avoid unsupported transformations.

If you need something like Date.EndOfWeek, do it via DAX calculated column in the model instead of Power Query.

EndOfWeek = ENDOFWEEK('Table'[deliverydate], SUNDAY)