Query not supported in DirectQuery (PowerBI / PowerQuery)

Question

A follow up from Display rows where foreign key is present in PowerBI

I now have the following query that retrieves data via a join and display as a table:

let
    Source = Sql.Database("server\database", "Mclaren"),
    dbo_Capability = Table.NestedJoin(
        Source{[Schema="dbo",Item="Capability"]}[Data],{"ID"},
        Source{[Schema="dbo",Item="Information_Group"]}[Data],{"Capability"},        
        "NewColumn",
        JoinKind.RightOuter
    ),
    #"Removed Columns" = Table.RemoveColumns(dbo_Capability,{"NewColumn"})
in
    #"Removed Columns"

Output: Output

As it is, data is being fetched via DirectQuery to enable live data to be displayed. However, Query Editor has thrown a warning message, saying "This step results in a query that is not supported in DirectQuery mode". Having googled up, I found that the questions and answers are unique on a case-by-case basis.

The issue is resolved by switching to "Import" mode, but I would prefer not to go down that path; I would rather change the query to allow what I want to do to still happen while playing fine with DirectQuery.

Thank you.

If you provide us with a minimal reproducible example it would be much easier and more pleasant to help. Could you please edit the question and include some DDL and DML to minimally reproduce the issue? — Jeroen
What type are both objects, Information_Group and Capability? — Eugene

Eugene Eugene · Accepted Answer · 2017-03-01T14:47:11

It seems that the cause of the problem is RightJoin. PowerBI doesn't support Direct Query for any types of join other than Left Join.

Try following:

let
    Source = Sql.Database("server\database", "Mclaren"),
    dbo_Capability = Table.NestedJoin(
        Source{[Schema="dbo",Item="Information_Group"]}[Data],{"Capability"},        
        Source{[Schema="dbo",Item="Capability"]}[Data],{"ID"},
        "NewColumn",
        JoinKind.LeftOuter
    ),
    #"Removed Columns" = Table.RemoveColumns(dbo_Capability,{"NewColumn"})
in
    #"Removed Columns"

Again, I see no reason in removing "NewColumn".

If both tables have no same columns, you may also find useful this approach:

let
    Source = Sql.Database("server\database", "Mclaren"),
    dbo_Capability = Table.Join(
        Source{[Schema="dbo",Item="Information_Group"]}[Data],{"Capability"},        
        Source{[Schema="dbo",Item="Capability"]}[Data],{"ID"},
        JoinKind.LeftOuter
    )
in
    dbo_Capability

The resulting table will be same as using LEFT JOIN in SQL.

Query not supported in DirectQuery (PowerBI / PowerQuery)

1 Answers