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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I could use a field name more complex than ‘A’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I can enter lots of fields in one record though.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I have converted my record to a table, so I should be able to use Record.FromTable() to go back to my original record.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image12.png/a1537847463e660a31158c8032525438.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image13.png/917da985be13220165c8d2823e95344f.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I have created a time 10:15.30, and I am going to convert this into a record.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
The M code I have used is:
= Time.ToRecord(Source)
This should convert the source time to a record.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image16.png/d082e3477129350b8a2a589156028e63.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
I have created a table from my record, and now I can specify just one row of that table to get a new record.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/201/image19.png/06909e3fbfc8faf87de7555c0b99e9d3.jpg)
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!