Forum Discussion
Dynamic PowerQuery using ADF
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.
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.
1 Reply
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.