Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Executing list of SQL queries stored in database table

Hello Everyone,

 

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

 

SrQuery
1Select ....
2Select ....
3Select ....
4Select ....

 

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.

Need Help
Tips and Tricks
Tutorial Requests

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    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