Function.From

D

T

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

Introduction to the Function.From M Function

The Function.From M function is a powerful tool that allows users to create custom functions in Power Query. This function takes in two parameters, the first being a record type that defines the function signature, and the second being a function that performs the desired transformation.

Record types are used to define the input and output of the function. They specify the data types of the function parameters and return values. The function that is passed as a parameter to Function.From is used to define the transformation logic.

Basic Syntax of Function.From

The basic syntax of the Function.From M function is as follows:


Function.From([functionType as type], [functionImplementation as function], [optional options as nullable record])


The functionType parameter is a record type that defines the input and output of the function. The functionImplementation parameter is a function that performs the desired transformation. The optional options parameter is used to specify additional options that control the behavior of the function.

Example Usage of Function.From

To illustrate the usage of Function.From, let's consider the following example. Assume we have a table that contains sales data for different products. We want to create a custom function that calculates the total sales for each product.

We can define the record type for our custom function as follows:


let

SalesRecord = [ProductID = 0, TotalSales = 0],

SalesFunctionType = type function (SalesRecord as any) as any

in

SalesFunctionType


This record type defines a function that takes in a SalesRecord parameter and returns a SalesRecord value.

Next, we can define the function implementation for our custom function as follows:


let

SalesFunction = (SalesRecord) =>

let

ProductID = SalesRecord[ProductID],

TotalSales = List.Sum(Table.SelectRows(SalesTable, each [ProductID] = ProductID)[SalesAmount]),

ResultRecord = [ProductID = ProductID, TotalSales = TotalSales]

in

ResultRecord

in

SalesFunction


This function takes in a SalesRecord parameter, extracts the ProductID, calculates the TotalSales for that product by filtering the sales table, and returns a SalesRecord with the ProductID and TotalSales values.

Finally, we can use the Function.From M function to create our custom function as follows:


let

SalesFunctionType = …,

SalesFunctionImplementation = …,

CustomSalesFunction = Function.From(SalesFunctionType, SalesFunctionImplementation)

in

CustomSalesFunction


In conclusion, the Function.From M function is a powerful tool that allows users to create custom functions in Power Query. The record type parameter defines the input and output of the function, and the function implementation parameter defines the transformation logic. By using Function.From, users can create custom functions that can be reused across multiple queries and workbooks, making their data transformation tasks more efficient and streamlined.

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)