Search code examples
azure-data-factorypowerquerym

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

I've looked at PowerQuery parameters but I don't see a way to populate these outside the PowerQuery.

** The select answer (1) was very helpful. A few notes since this was done in an ADF pipeline, not Excel:
• 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.

Solution

  • In powerquery you can

    (1) Pull text from the main excel sheet by creating a range name, and pulling that through. The contents of the range name could be populated from a VBA or a form, or whatever

    For example, create range name aaa in the excel sheet and populate it using AFG, VBA, or some other method. Pull the final contents on that range into powerquery per 2nd row in this example, subsequently used in 3rd row in place of hardcoded filepath

    let
NameValue= Excel.CurrentWorkbook(){[Name="aaa"]}[Content]{0}[Column1],
Source = Table.FromColumns({Lines.FromBinary(File.Contents(NameValue), null, null, 1252)})
in Source

    (2) generate the entire powerquery iself using VBA

    (3) load and execute powerquery code contained in an external text file that you create

    let
//Load M code from text file
// https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/
Source = Text.FromBinary(File.Contents("C:\directory\MyMCode.txt")),
//Evaluate the code from the file as an M expression
EvaluatedExpression = Expression.Evaluate(Source, #shared)    
in
EvaluatedExpression