Record.HasFields

D

Back to top

T

Back to top

#

Back to top

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

Understanding Records in Power Query

Before diving into the M code behind the Record.HasFields function, it’s important to understand what records are in Power Query. Records are a type of data structure that can contain multiple key-value pairs. Each key-value pair is called a field. Records are used to store complex data structures, such as JSON, and are a fundamental part of Power Query.

The M Code Behind Record.HasFields

The M code behind the Record.HasFields function is relatively simple. The function takes two arguments, a record and a list of field names. It returns a Boolean value indicating whether the record contains all of the specified fields. Here is the M code for the Record.HasFields function:

Record.HasFields = (record, fieldNames) => List.ForAll(fieldNames, each Record.HasFields(record, _))

The function uses the List.ForAll function to iterate over the list of field names, checking if each field exists in the record. If all of the fields exist, the function returns true. If any of the fields are missing, the function returns false.

Using Record.HasFields in Power Query

Now that we understand the M code behind the Record.HasFields function, let's look at some examples of how it can be used in Power Query.

Example 1: Checking if a Record has a Field

Suppose we have a record that contains information about a customer, including their name, address, and phone number. We want to check if the record contains a field called "email". Here is the M code we can use:

let

customer = [Name = “John Smith”, Address = “123 Main St”, Phone = “555-555-5555”],

hasEmail = Record.HasFields(customer, {“email”})

in

hasEmail

In this example, the customer record does not contain an "email" field, so the Record.HasFields function returns false.

Example 2: Checking if a Record has Multiple Fields

Suppose we have a record that contains information about a customer, as well as their order history. We want to check if the record contains fields for both the customer's name and their most recent order date. Here is the M code we can use:

let

customer = [Name = “John Smith”, OrderHistory = [OrderDates = {“2021-01-01”, “2021-02-01”, “2021-03-01”}]],

hasNameAndOrderDate = Record.HasFields(customer, {“Name”, “OrderHistory.OrderDates{0}”})

in

hasNameAndOrderDate

In this example, the Record.HasFields function checks if the record contains both the "Name" field and the first order date in the "OrderDates" list. Since both fields exist in the record, the function returns true.

Example 3: Filtering Records Based on Field Existence

Suppose we have a table of customer data and we want to filter the table to only include records that have an email address. Here is the M code we can use:

let

customers = Table.FromRows({{“John Smith”, “123 Main St”, “555-555-5555”, null}, {“Jane Doe”, “456 Elm St”, “444-444-4444”, “jane.doe@example.com”}}, {“Name”, “Address”, “Phone”, “Email”}),

filteredCustomers = Table.SelectRows(customers, each Record.HasFields(_, {“Email”}))

in

filteredCustomers

In this example, we use the Record.HasFields function within the Table.SelectRows function to filter the table. The function checks if each record contains an "Email" field and only includes records that do.

The Record.HasFields function is a powerful tool in the Power Query language that allows you to manipulate data based on the existence of specific fields in records. By understanding the M code behind this function, you can use it to filter and transform data in a variety of ways.

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)