Table.FromValue - Table Function

Table.FromValue is a Power Query M function that creates a table with a column containing the provided value or list of values. The function returns a table with a single column containing the input values.

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

Syntax

Table.FromValue(
   value as any,
   optional options as nullable record,
) as table

Description

Table.FromValue creates a table with a column from the provided value(s). It forms a table with a column containing value or a list of values. Optional options can control column names and other settings: DefaultColumnName represents the name of the column used when creating a table from a list or scalar value.

The function can convert different types of data into a table, with behavior varying depending on the input type:

Scalar Values: Converts a single value into a table with one column and one row.
Lists: Converts a list into a table with one column, where each item in the list becomes a row in the table.
Records: Converts a record into a table with two columns: one for the keys (labeled “Name”) and one for the values (labeled “Value”).

Examples

Let’s explore how the Table.FromValue function works with various data types through some practical examples.

Converting Scalar Values

A scalar value, such as a single text string or number, can be directly converted into a single-column, single-row table:

Table.FromValue( "Rick" )

This expression produces a table with one column named “Value” by default, containing the value "Rick".

Table.FromValue turns scalar value into table in Power Query M

By default, the function names this column ‘Value’.

If you want to customize the column name, you can use the optional DefaultColumnName parameter:

Table.FromValue(
  "Rick", 
  [ DefaultColumnName = "Letter" ]
)

This expression returns a table with one column named "Name" that contains the value "Rick".

Table.FromValue provides default column name in Power Query M

Converting Lists

Lists are converted into tables with a single column, where each list item becomes a row:

Table.FromValue( { "Rick", "Power Query" } )

The code returns a table with one column containing two rows containing the values “Rick” and “Power Query”.

Table.FromValue accepts list of values in Power Query M

You can also specify a custom column name for the resulting table:

Table.FromValue( 
  { true, false }, 
  [ DefaultColumnName = "True or False" ]
)

This creates a table with one “True or False” column containing two rows: true and false.

Converting Records

When converting a record, the Table.FromValue function creates a table with two columns: one for the record’s keys (labeled “Name”) and one for its values (labeled “Value”):

Table.FromValue( [ 1 = "Apple" ] )

This returns a table with two columns—”Name” and “Value”—and one row containing 1 and "Apple".

Table.FromValue accepts records in Power Query M

For a record with multiple key-value pairs, the function returns a table with multiple rows:

Table.FromValue( [ 1 = "Apple", 2 = "Prume" ] )

The resulting table has two columns—”Name” and “Value”—and two rows: one for 1 = "Apple" and another for 2 = "Prume".

Table.FromValue accepts multiple items in a record in Power Query M

Converting a List of Objects

If you input a list of objects (such as lists, records, or tables), Table.FromValue will create a table with a single column, where each object becomes a row:

Table.FromValue(
  {
    [ ProductKey = 1, Product = "Apple" ],
    [ ProductKey = 2, Product = "Prume" ]
  }
)

This returns a table with one column containing two rows, each row holding a record with ProductKey and Product values.

Table.FromValue accepts list of record in Power Query M

Similarly, if you input a list of lists:

Table.FromValue( 
  {
    { "ProductKey", 1 },
    { "ProductKey", 2 }
  } 
)

This produces a table with one column containing two rows, where each row holds a list.

Table.FromValue accepts list of lists in Power Query M

Learn more about Table.FromValue in the following articles:

Create Tables from Scratch in Power Query M (40+ Examples)
Creating tables from scratch in Power Query can be tricky, but this post shows you how. You learn how to work with lists, records and much more! » Read more

Other functions related to Table.FromValue are:

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