Executing list of SQL queries stored in database table

[Anonymous]

Hello Everyone,

I'm looking for an apporach to load data into Power BI from list of SQL queries stored in Postgres database table.

Sr	Query
1	Select ....
2	Select ....
3	Select ....
4	Select ....

Essentially, we need to automate query loading process as list of query will increase/decrease with time & manual addition would not be permanent solution.

Any inputs if this can be achived & appreciate if you can share your experiences in such scenarios.

[OwenAuger]

Hi @Anonymous 

You can certainly do this sort of thing by first querying the table containing the queries, then using a function like Value.NativeQuery to execute each query.

I have attached a small PBIX with parameters you could update to test against your database. I have tested this successfully with a PostgreSQL database on my local machine.

I have assumed you want to union the results of the queries. If you want to do something different, you would need to change the final steps of CombinedQueries.

Below is the M code for the queries in the PBIX. The four parameters should be changed as needed for your data source.

// Server
"localhost" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

// Database
"OwenSandbox" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

// Schema
"public" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

// Table
"MyQueries" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

// DatabaseConnection
let
    Source = PostgreSQL.Database(Server, Database)
in
    Source

// CombinedQueries
let
    Source = DatabaseConnection,
    QueryTable = Source{[Schema=Schema,Item=Table]}[Data],
    #"Add Query Result" = Table.AddColumn(QueryTable, "Query Result", each Value.NativeQuery(DatabaseConnection,[Query],null,[EnableFolding = true])),
    #"Select Query Result" = Table.SelectColumns(#"Add Query Result",{"Query Result"}),
    // Union the query results. Change as required.
    Combine = Table.Combine(#"Select Query Result"[Query Result])
in
    Combine

Hopefully that helps a bit! Please post back if needed 🙂

Regards