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!
