Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Going for the Record

7 October 2020

Welcome to our Power Query blog.  This week, I put the spotlight on records.

 

This week, I am going to look at several ways to create a record.

The first and perhaps simplest method is to define a record in M.  I have created a record as below:

The M code I have used is:

= [A = "Kathryn", B = "Record"]

In other words, create a record with two fields, A and B, where A is “Kathryn” and B is “Record”.  The result is a record.

I could use a field name more complex than ‘A’. 

The M code I have used is:

= [this is the first field = "Kathryn", this is the second field = "Record"]

I can do this without surrounding my field name with quotes (“”), and I do not need to include a #, even if there are spaces in my field name. 

I can also create fields of different types without specifying a datatype.  The M code I used in the previous screenshot is:

= [this is the first field  = "Kathryn", this is the second field which is a number = 1.5]

However, I can’t use the same field name in a record.

I can enter lots of fields in one record though.

The M code I have used is:

= [field 1  = "Kathryn", field 2 = 1.5, field 3 = [inset field 1 = "hello", inset field 2 = "world"]]

In this case, my record has three fields, and the last field is a record, which itself contains two fields.

I can also use M functions to create records.  There are many functions which output records, so I will look at just a few of them.  I will start with Record.FromTable():

Record.FromTable(table as table) as record 

This returns a record from a table containing field names and value names [Name = name, Value = value]. An exception is yielded if the field names are not unique.

I have converted my record to a table, so I should be able to use Record.FromTable() to go back to my original record.

The M code I enter is:

= Record.FromTable( #"Converted to Table")

where “Converted to Table” is my previous step.  I enter this to get the following result:

My table has been converted into a record.  I can also create a record from a list using Record.FromList():

Record.FromList(list as list, fields as any) as record

This returns a record given a list of field values and a set of fields.  The fields can be specified either by a list of text values, or a record type.  An error is generated if the fields are not unique.

The M code I have used is:

= Record.FromList(Source, {"Fruit 1","Fruit 2","Fruit 3","Fruit 4"})

This should create a record from my list values and the list of field names that I have specified.

I have the record I expected.  I can also use the function Record.Combine():

Record.Combine(records as list) as record

This combines the records in the given records.  If the records contain non-record values, an error is returned.

I can combine the records I have created:

The M code I have used is:

= Record.Combine({Custom1,Query1})

This should combine the record I just created in step Custom1, and the record I created in Query1.

The records have been combined to create a new record with seven fields.

There are other Power Query M functions that output records, such as Time.ToRecord():

Time.ToRecord(time as time) as record

This returns a record containing the parts of the given time:

  • time: this is a time value for from which the record of its parts is to be calculated.

I have created a time 10:15.30, and I am going to convert this into a record.

The M code I have used is:

= Time.ToRecord(Source)

This should convert the source time to a record.

A record has been created with field names “Hour”, “Minute”, and “Second”.

Finally, I can get a record by specifying a row of a table:

I have created a table from my record, and now I can specify just one row of that table to get a new record.

The M code I have used is:

= #"Converted to Table"{4}

This should get the fifth row of my table and present it as a record, since the count starts at 0.

The fifth row has been converted to a record with field names ‘Name’ and ‘Value’, and field values ‘Field 1’ and ‘Kathryn’ respectively.

 

Come back next time for more ways to use Power Query!

Newsletter