Record.FromTable

Record.FromTable is a Power Query M function that converts a table of records with field names and value names into a single record. The function returns a record created from the input table, or throws an exception if the field names are not unique.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Record.FromTable( table as table ) as record

Description

Record.FromTable constructs a record from a table where each row is a record formatted as {[Name = name, Value = value]}. Uniqueness of field names is mandatory, and duplicates will result in an error.

Examples

So what’s a practical example where we can use Record.FromTable? Let’s say you’re trying to use the Record.FieldOrDefault function to lookup a value from a record. If the record has few values, you can easily maintain that manually. With an increasing amount of data, however, it may become cumbersome.

In that situation, you may consider storing the record field names and values within a table. If you then require the data in record form for Record.FieldOrDefault, Record.FromTable can turn your table into a record. So how does that work?

To be able to use the Record.FromTable function, you need to pass it a table that has two columns. A Name column and a Value column. The following code creates such a table:

let
  mytable = 
    #table( 
      type table[ Name = Text.Type, Value = Text.Type ],
      { 
        { "UK", "United Kingdom" },
        { "NL", "Netherlands"    },
        { "FR", "France"         }
      }  
    )
in
  mytable

The resulting table has a Name column containing country abbreviations UK, NL and FR. It also has a Value column with the full names United Kingdom, Netherlands and France.

You can pass this value to the Record.FromTable function as follows:

let
  mytable = 
    #table( 
      type table[ Name = Text.Type, Value = Text.Type ],
      { 
        { "UK", "United Kingdom" },
        { "NL", "Netherlands"    },
        { "FR", "France"         }
      }  
    ),
  recordFromTable = Record.FromTable( myTable )
in
  mytable

/* Output: 
    [ UK  = "United Kingdom",  
      NL  = "Netherlands",
      FR = "France",         ] */

This code returns the following record with three field names and three field values:

In this shape, you can easily pass it to the Record.FieldOrDefault function. Note that in this example we created the table manually. In real life scenarios, your table may come from a database or another application.

Related functions

Other functions related to Record.FromTable are:

• Record.FromList
• Record.ToList
• Record.ToTable

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/record-fromtable