Forum Discussion

markwclancy's avatar
markwclancy
Frequent Visitor
3 years ago
Solved

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 fo...
Need Help
  • markwclancy's avatar
    markwclancy
    3 years ago

    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.

markwclancy's avatar
markwclancy
Frequent Visitor
3 years ago

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.