Table.ViewFunction

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.ViewFunction

One of the functions that makes heavy use of M code is Table.ViewFunction. This function allows you to create a custom view of a table, which can be useful for tasks such as data cleaning, filtering, and formatting.

Understanding the Table.ViewFunction Function

The Table.ViewFunction function is used to create a custom view of a table. It takes two arguments: a table to create the view from, and a function that defines the view. The function takes a single argument, a row from the table, and returns a record that represents the transformed row.

Here’s an example of a simple view function that takes a table of sales data and returns only the rows where the sales amount is greater than 1000:

let

salesTable = #table(

{"Region", "Product", "Sales"},

{

{"North", "Product A", 500},

{"North", "Product B", 1500},

{"South", "Product A", 750},

{"South", "Product B", 2000}

}

),

filterSales = Table.ViewFunction(salesTable, each if [Sales] > 1000 then _ else null)

in

filterSales

In this example, the Table.ViewFunction function is used to create a new table called `filterSales` that contains only the rows where the sales amount is greater than 1000. The view function used here is `each if [Sales] > 1000 then _ else null`, which returns the input row if the sales amount is greater than 1000, and null otherwise.

Writing Custom View Functions

One of the most powerful features of Table.ViewFunction is the ability to write custom view functions. These functions can be used to transform data in a wide variety of ways, including filtering, sorting, aggregating, and formatting.

Here’s an example of a custom view function that takes a table of customer data and returns a new table with the following columns:

– `FullName`: The customer’s full name, concatenated from the `FirstName` and `LastName` columns.

– `Age`: The customer’s age, calculated from the `BirthDate` column.

– `State`: The customer’s state, calculated from the `City` and `StateCode` columns.

let

customerTable = #table(

{"FirstName", "LastName", "BirthDate", "City", "StateCode"},

{

{"John", "Doe", #date(1980, 1, 1), "Seattle", "WA"},

{"Jane", "Doe", #date(1985, 2, 2), "Portland", "OR"},

{"Mike", "Smith", #date(1990, 3, 3), "San Francisco", "CA"},

{"Lisa", "Johnson", #date(1975, 4, 4), "New York", "NY"}

}

),

customView = Table.ViewFunction(customerTable, each {

"FullName" = [FirstName] & " " & [LastName],

"Age" = Duration.TotalDays(DateTime.LocalNow() - [BirthDate]) / 365,

"State" = [City] & ", " & [StateCode]

})

in

customView

In this example, the custom view function is defined using the `each` keyword, which allows us to refer to the input row using the underscore symbol (`_`). The function returns a record with three fields: `FullName`, `Age`, and `State`, which are calculated based on the values in the input row.

The Table.ViewFunction function is a powerful tool for transforming data in Power Query. By writing custom view functions, you can create custom views of your data that can be used for a wide variety of tasks, including data cleaning, filtering, and formatting. Whether you’re a beginner or an experienced Power Query user, it’s worth taking the time to learn how to use this powerful function to its fullest potential.

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)