Record.FieldNames

D

Back to top

T

Back to top

#

Back to top

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

Understanding Records in Power Query

Before we dive into the `Record.FieldNames` function, let’s first understand what records are in Power Query. A record is a collection of fields that represent a single data item. Each field in a record contains a value that corresponds to a specific property. For example, a record can represent a customer and contain fields such as ‘Name’, ‘Address’, ‘Phone Number’, and ‘Email Address’.

In Power Query, records are represented using curly braces {} and are separated by commas. The fields within a record are separated by a semicolon (;) and are represented as field name/value pairs. Here is an example of a record:

{ Name = “John Smith”; Age = 35; Occupation = “Engineer” }

The Record.FieldNames Function

The `Record.FieldNames` function is a built-in function in Power Query that returns a list of all the field names in a given record. The function takes a record as its argument and returns a list of text values. Here is the syntax of the `Record.FieldNames` function:

Record.FieldNames(record as record) as list

The `record` argument is the record for which to return the field names. The function returns a list of text values representing the field names in the record.

Here is an example of using the `Record.FieldNames` function to return the field names of a record:

let

customer = { Name = “John Smith”; Age = 35; Occupation = “Engineer” },

fieldNames = Record.FieldNames(customer)

in

fieldNames

In the above example, we define a record called `customer` and use the `Record.FieldNames` function to return a list of field names. The output of this query will be a list of text values as follows:

{ “Name”, “Age”, “Occupation” }

Using the Record.FieldNames Function in Practice

Now that we understand the basics of the `Record.FieldNames` function, let’s explore some practical examples of how it can be used in Power Query.

Example 1: Renaming Columns Using Field Names

One common use case for the `Record.FieldNames` function is to rename columns in a table using the field names from a record. Here is an example of how this can be done:

let

customerTable = Table.FromRecords(

{

[ Name = “John Smith”; Age = 35; Occupation = “Engineer” ],

[ Name = “Jane Doe”; Age = 28; Occupation = “Accountant” ]

}

),

fieldNames = Record.FieldNames( customerTable{0} ),

renamedColumns = List.Transform( fieldNames, each Text.Replace(_, ” “, “”) ),

renamedTable = Table.RenameColumns( customerTable, List.Zip( { fieldNames, renamedColumns } ) )

in

renamedTable

In the above example, we first define a table called `customerTable` that contains records representing customers. We then use the `Record.FieldNames` function to retrieve the field names of the first record in the table. We then use the `List.Transform` function to remove spaces from the field names and create a new list of renamed columns. Finally, we use the `Table.RenameColumns` function to rename the columns in the table using the field names and the new column names.

Example 2: Dynamically Pivoting Data Using Field Names

Another use case for the `Record.FieldNames` function is to dynamically pivot data based on the field names in a record. Here is an example of how this can be done:

let

customerTable = Table.FromRecords(

{

[ Name = “John Smith”; Age = 35; Occupation = “Engineer” ],

[ Name = “Jane Doe”; Age = 28; Occupation = “Accountant” ]

}

),

fieldNames = Record.FieldNames( customerTable{0} ),

pivotTable = Table.Pivot( customerTable, fieldNames, “Value” )

in

pivotTable

In the above example, we first define a table called `customerTable` that contains records representing customers. We then use the `Record.FieldNames` function to retrieve the field names of the first record in the table. We then use the `Table.Pivot` function to pivot the data based on the field names in the record. This allows us to dynamically pivot the data based on the fields present in the record, without hardcoding specific column names.

The `Record.FieldNames` function is a powerful tool for working with records in Power Query. It allows you to dynamically retrieve the field names of a record and use them in your data transformation workflows. By understanding the M code behind this function and its practical applications, you can enhance your data transformation skills and become more efficient in your Power Query projects.

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)