Dynamic PowerQuery using ADF

[markwclancy]

Using ADF Pipelines, is there a way to pass parameters (or the entire M query) into the PowerQuery itself?

Here's a description of my challenge:
• I'm dealing with a dynamic data model from a form builder type application so the tables and column names are stored as data
• I'm generating PowerQuery code dynamically using Python as an initial activity in an ADF pipeline. There will be dozens of these PowerQuery scripts.
• I want to pass the PowerQuery code directly into the PowerQuery activity

• I don't see any way to make the PowerQuery dynamic --- it only seems to allow me to manually enter the PowerQuery

Any ideas/assistance would be appreciated.

[markwclancy · Accepted solution]

I was able to get this working as follows:

• A new activity was created that wrote the "parameters" for the PowerQuery into an Azure SQL table (with just "key" and "value" columns)

• A new query was created in PowerQuery that loaded the parameters from the table

•The parameters were assigned to variables in the PowerQuery -- e.g. groupFields = PowerQuery{[Key = "groupFields"]}[Value]

With this approach, the same PowerQuery code to generate multiple tables, (some with 100+ columns) with no hard-coding of the columns names.