Function.Invoke

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Function.Invoke

In this article, we’ll take a deep dive into the M code behind `Function.Invoke`. We’ll explore how it works, what it can do, and how to use it effectively in your Power Query queries.

Understanding `Function.Invoke`

At its core, `Function.Invoke` is a function that takes two arguments: a function to invoke, and a record of arguments to pass to that function. Here’s a simple example:

let

fn = (a, b) => a + b,

args = [a = 1, b = 2],

result = Function.Invoke(fn, args)

in

result

In this example, we define a simple function `fn` that takes two arguments and returns their sum. We also define a record of arguments `args` that sets the values of `a` and `b`.

Finally, we call `Function.Invoke` and pass in `fn` and `args`. The result is the sum of `a` and `b`, which is `3`.

Using `Function.Invoke` for Dynamic Function Calls

So why is `Function.Invoke` useful? The primary benefit is that it allows you to call functions dynamically at runtime, rather than statically defining them in your query.

This is particularly useful when you're dealing with data that has varying shapes or structures. For example, consider a dataset that contains different columns depending on the source. Using `Function.Invoke`, you can create a query that automatically adapts to these varying structures.

Here's a simple example. Let's say we have two tables, `TableA` and `TableB`, and we want to merge them together. However, the two tables have different columns:

let

TableA = Table.FromRecords({

[ID = 1, Name = “Alice”],

[ID = 2, Name = “Bob”]

}),

TableB = Table.FromRecords({

[ID = 1, Age = 30],

[ID = 2, Age = 40]

}),

MergeTables = Function.Invoke(Table.Combine, {[TableA, TableB]})

in

MergeTables

In this example, we use `Function.Invoke` to dynamically call the `Table.Combine` function, passing in both `TableA` and `TableB`. This allows us to merge the two tables together, even though they have different columns.

Passing Arguments Dynamically

Another benefit of `Function.Invoke` is that it allows you to pass arguments dynamically. This can be useful in scenarios where you don't know the exact arguments that will be needed until runtime.

Here's an example. Let's say we have a dataset with a column called `Filepath`, which contains the file path of some data we want to load. However, the file type can vary (e.g. CSV, Excel, JSON), and we want to be able to load any file type dynamically.

We can accomplish this with `Function.Invoke` by defining a function that takes the file type as an argument, and dynamically calling the appropriate loading function based on the file type:

let

LoadData = (filepath, filetype) =>

let

loadfn = if filetype = “csv” then Csv.Document else if filetype = “excel” then Excel.Workbook else if filetype = “json” then Json.Document else null,

data = if loadfn <> null then Function.Invoke(loadfn, {filepath}) else null

in

data,

Source = Table.FromRecords({

[Filepath = “data.csv”, FileType = “csv”],

[Filepath = “data.xlsx”, FileType = “excel”],

[Filepath = “data.json”, FileType = “json”]

}),

LoadedData = Table.AddColumn(Source, “Data”, each LoadData([Filepath], [FileType]))

in

LoadedData

In this example, we define a function `LoadData` that takes two arguments: the file path and the file type. We use `Function.Invoke` to dynamically call the appropriate loading function based on the file type.

In summary, `Function.Invoke` is a powerful function that enables you to dynamically call any other M function, passing in arguments as needed. This allows you to create more flexible and reusable queries that can adapt to varying data shapes and structures.

By understanding the M code behind `Function.Invoke`, you can unlock the full potential of this powerful function. So start experimenting with `Function.Invoke` in your Power Query queries today, and see how it can help you work more efficiently and effectively with your data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)