Record.SelectFields

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Record.SelectFields

Understanding Records in Power Query

Before we can delve into the Record.SelectFields function, it is important to understand what a record is in Power Query. A record is a collection of named values, similar to a row in a database table. Each value is assigned a unique name or key, and can contain any data type, including numbers, text, and arrays.

For example, consider the following record:

[Name=”John”, Age=35, Email=”john@example.com”]

This record contains three named values: Name, Age, and Email. The value of each key can be accessed by using dot notation, like so:

Record.Name // Returns “John”

Record.Age // Returns 35

Record.Email // Returns “john@example.com”

The Record.SelectFields Function

The Record.SelectFields function is a built-in function in Power Query that allows you to select a subset of columns from a record. This function takes two arguments: the record to be filtered, and a list of keys to include in the filtered record.

Here is the basic syntax for the Record.SelectFields function:

Record.SelectFields(Record, {“Key1”, “Key2”, …, “KeyN”})

For example, let's say we have the following record:

[Name=”John”, Age=35, Email=”john@example.com”, Phone=”555-1234″]

If we only want to keep the Name and Email columns, we can use the Record.SelectFields function like this:

Record.SelectFields([Name=”John”, Age=35, Email=”john@example.com”, Phone=”555-1234″], {“Name”, “Email”})

This will return a new record with only the Name and Email columns:

[Name=”John”, Email=”john@example.com”]

Using Record.SelectFields in Power Query

The Record.SelectFields function can be incredibly useful when working with large datasets in Power Query. It allows you to quickly and easily filter out unnecessary columns, reducing the size of your data and making it easier to work with.

For example, let's say we have a dataset containing information on employees, including their name, age, salary, and department. If we only want to keep the name and department columns, we can use the Record.SelectFields function to filter out the rest of the data:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45W8svMS1ZwUQrMlSjIzy/Kz0wpVQrMlNSMjQwMjYxMzKwMkswNzC1MjQwMjYxMzKwMkswNzC1MwA=”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Salary = _t, Department = _t]),

#”Filtered Rows” = Table.SelectColumns(Source,{“Name”, “Department”})

in

#”Filtered Rows”

This code uses the Table.SelectColumns function to select only the Name and Department columns from the Source table. The resulting table will only contain these two columns, making it easier to work with and analyze.

The Record.SelectFields function is a powerful tool for filtering and manipulating data in Power Query. By selecting only the columns you need, you can reduce the size of your data and make it easier to work with. Whether you are working with small or large datasets, understanding the M code behind this function can help you save time and streamline your data analysis workflow.

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)