Record.FieldValues

D

Back to top

T

Back to top

#

Back to top

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

What is Record.FieldValues?

Record.FieldValues is a M function that is used to extract all the values from a record and return them as a list. This function is especially useful when working with nested records, where you need to extract values from a record that is inside another record.

Syntax

The basic syntax of the Record.FieldValues function is as follows:

Record.FieldValues(record as record) as list

The function takes a record as its input and returns a list of all the values in the record.

How to use Record.FieldValues

To use the Record.FieldValues function, you need to provide a record as input. The record can be created using the curly brace notation, as shown below:

let

myRecord = [name = “John”, age = 30, address = [street = “Main Street”, city = “New York”]]

in

myRecord

This will create a record with three fields - name, age, and address. The address field is itself a record with two fields - street and city.

To extract all the values from this record, you can use the Record.FieldValues function as follows:

let

myRecord = [name = “John”, age = 30, address = [street = “Main Street”, city = “New York”]],

myValues = Record.FieldValues(myRecord)

in

myValues

This will return a list of all the values in the record:

{“John”, 30, [street = “Main Street”, city = “New York”]}

Working with nested records

As mentioned earlier, the Record.FieldValues function is especially useful when working with nested records. Consider the following record:

let

myRecord = [name = “John”, age = 30, address = [street = “Main Street”, city = “New York”]],

myNestedRecord = [record1 = myRecord, record2 = [name = “Jane”, age = 25, address = [street = “Broadway”, city = “Los Angeles”]]]

in

myNestedRecord

This record contains two fields - record1 and record2. The record1 field is the same record we created earlier, and the record2 field is a new record with its own set of fields.

To extract all the values from both these records, you can use the Record.FieldValues function as follows:

let

myRecord = [name = “John”, age = 30, address = [street = “Main Street”, city = “New York”]],

myNestedRecord = [record1 = myRecord, record2 = [name = “Jane”, age = 25, address = [street = “Broadway”, city = “Los Angeles”]]],

myValues = List.Combine({Record.FieldValues(myNestedRecord[record1]), Record.FieldValues(myNestedRecord[record2])})

in

myValues

This will return a list of all the values from both records:

{“John”, 30, [street = “Main Street”, city = “New York”], “Jane”, 25, [street = “Broadway”, city = “Los Angeles”]}

The Record.FieldValues function is a powerful tool in Power Query that allows you to extract all the values from a record and work with them in a more flexible way. By understanding how to use this function, you can make your Power Query workflows more efficient and effective.

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)