[SOLVED] Power BI - Dynamically generate queries

Power BI - Dynamically generate queries

I'm using Power BI desktop to import data from an Azure Blob, and then generate some queries to share on the online portal. One of my intermediate queries generates a table of [name][table] like:

Query: ALL TABLES

name  | table 
--------------
name1 | Table1
name2 | Table2
name3 | Table3

The names are unique (grouped by name in previous query step), and the table links all point to a table I want to use in a new query. Currently I'm having to manually generate new queries from each Table, and then apply a custom query function on the query source to get the desired final query to export, eg.:

Query: NAME1

let
    Source = #"ALL TABLES",
    #"Name1" = DoSomething(Source, 0)
in
    #"Name1"

Query: NAME2

let
    Source = #"ALL TABLES",
    #"Name2" = DoSomething(Source, 1)
in
    #"Name2"

etc.

This is ok for now as we're setting up, we don't have that many entries. However as soon as we start putting real data into the Azure Blob the number of rows and queries we need to generate will start growing, and doing it by hand is un-manageable.

Ideally I'd be able to add a last step to the ALL TABLES query, that goes through all the rows and generates new queries from the tables in them. This list will also potentially change with each update, so ideally this should automatically generate the new queries when the data is reloaded form the Azure Blob.

Is there a way to do this from a Power BI query? Alternatively, does anyone where I might look on how to make an external script generate this on a power BI project (say, an automated script run daily from our build server)?

Solution

For truly dynamic environments like the one you're describing I would recommend you use a direct query source like azure SQL to load your data. When you do that the schema changes on your back end are immediately available to your users without needing to recreate queries.

If you continue down the route you're on, there is no purely automatic way to add new queries to your Power BI desktop files. When you add a new event, you'll need to edit your file, add the new query and ensure it is added to the data model.