Dynamic SQL data import and directquery

[Anonymous]

Hi,

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 containing 3 collumns (schema,id_file,tablename) to get each table that i need but this steps gives me 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: 

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.

Thanks.

[Icey]

Hi @Anonymous ,

Please refer to this post to ensure that your tables remained in DirectQuery mode.

Best Regards,

Icey

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

[Anonymous]

I edited the issue, the solution might be not calling custom functions?