Dynamic SQL data import and directquery

Question

I would like to build reports based on information contained in tables on a SQL Server. The SQL Server contains multiple tables (one for each datatype).

I have written this function in power query :

let
    GetTableFromDataBase = (schema as text, id_file as text, tablename as text) => let
    Source = Sql.Database("xxx.database.windows.net", "xxx", [Query="SELECT * FROM "&schema&"."&tablename&" WHERE [id_file]="&id_file])
in
    Source
in
    GetTableFromDataBase

Then I invoked the function on a query with 3 columns (schema, id_file, tablename) to get each table that I need but this steps I get this warning message:

Step results in a query not supported in DirectQuery Mode

Is there any way to do this without losing the DirectQuery mode?

EDIT: I tried some other ways to make dynamic import using directquery, this query (using parameters) is supported in DirectQuery:

let
    Source = Sql.Database(ServerAdress, DbName),
    GoTable = Table.SelectRows(Source{[Schema=schema,Item=tablename]}[Data],each [id_file]=id_file)
in
    GoTable

But if I take the same query and convert it to a function then call it using the same parameters PowerBi switches to import mode, this is a simple query why wouldn't it be DirectQuery compatible?

Thanks

Alexis Olson Alexis Olson · Accepted Answer · 2020-11-04T19:16:54

Any transformations applied to a DirectQuery in the query editor must be passed back to the SQL server. Power BI knows how to do this with basic operations like selecting and filtering columns but it cannot apply any arbitrary function because it doesn't necessarily know how that translates into SQL. In particular, a user-defined custom function is essentially a black box transformation from a SQL perspective, even if it seems obvious how it should be translated.

The reason it works with a parameter is that once those parameters are set, they're just constant values that don't require inserting a step that it doesn't know what to interpret in SQL.

Dynamic SQL data import and directquery

1 Answers