Creating New Dynamic Queries or Blank Queries from within an Existing Power Query Query

[Sussibar]

I am thinking I already know the answer to this, but does anyone know if it is possible to create a new query from an existing query? I.e. Does the language spec have a means of calling the UI equivalent of NewSource -> Blank Query found in the Transform Data/Get & Transform/Edit Queries ETL editor of Power BI from within an existing Power Query script (and not by clicking the UI)?

New Blank Query UI Button

I was hoping there might be a way to utilize #shared and/or #sections intrinsic variables of the global environment, possibly with Expression.Evaluate() to dynamically generate a set of new tables from an existing table based on applying logic with the each keyword to filter a column's distinct values.

An example use case:

There is a budget worksheet that has [Department] column, whose distinct values change over time. For each department in that column, I want to filter the existing worksheet to contain only the records with the current department and then create a new query that creates a new table based on that filtered result, which would create a new query in the Queries sidebar (effectively creating a new record in #sections). 

My intuition is that the functionality I'm looking for is a property of the UI/application and not the language spec. I was hoping whatever UI function mapping that is called to instantiate a new power query (which I'm hoping behind the scenes is some function to create a new record in Source[Section1]) was exposed to the end user, but so far I haven't had any luck.

Example of an intrinsic section in Source

Example section Source[Section1] in global environment intrinsic variable #sections

If you have any idea whether this is possible. Please let me know. It might be something that I just need to manually do. It would be an elegant solution to dynamically instantiate new queries from existing queries, but I don't know if that fits within a functional programming paradigm. Is creating a new query something that is only possible behind the scenes and only accessible via the UI?

[Anonymous]

Hi,

This may be oversimplified, but you can reference a query in the same model by right clicking the original query and choosing "Reference Query" or in the new query type the name of the query you are referencing in the Source line.

 let
   Source = "Original Query Name"

Hope that helps!

~Kim

[lbendlin]

No, Power Query doesn't have eval() functions. What it does have though is each and _ - those allow you to iterate over arrays and contexts so you can create pretty elaborate logic all with a little lookup table (for example a list of active departments for which you want to do the ETL)

A lot of the Power Query functions accept custom functions as parameters - another very powerful concept that will help you.