Table.FromList

Updated on
Rick de Groot

Table.FromList is a Power Query M function that converts a list into a table by applying an optional splitting function to each item in the list. The function returns a table derived from the input list.

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

Syntax

Table.FromList(
   list as list,
   optional splitter as nullable function,
   optional columns as any,
   optional default as any,
   optional extraValues as nullable number,
) as table
ArgumentAttributeDescription
ListThe list of values to create a table from.
SplitterOptionalThe splitter function to apply to the values in the list. By default, the list is assumed to be a list of text values that is split by commas.
ColumnsOptionalSpecifies the column. Can be provided as number of columns, list of columns or as TableType.
DefaultOptionalWhen omitting this argument, missing values show as null. Alternatively, you can provide a default value to show instead of null.
ExtraValuesOptionalProvide the ExtraValues.Type to specify the expected action for extra values in a row that has less columns than expected. You can choose from ExtraValues.Error, ExtraValues.Ignore or ExtraValues.List.

Description

Converts a list, list into a table by applying the optional splitting function, splitter, to each item in the list. By default, the list is assumed to be a list of text values that is split by commas. Optional columns may be the number of columns, a list of columns or a TableType. Optional default and extraValues may also be specified.

Examples

Using Table.FromList you can easily create a single column table by using only the first argument. Below formula returns a column called Column1 of data type any.

Table.FromList with single column without data types or column names

It’s important to remember that Table.FromList requires a text-based input. Feeding the function with values other than text, such as numbers, will result in an error.

Table.FromList error when not receiving text values.

The function’s flexibility extends with the option to add a second and third argument. To instruct the function to avoid any custom splitting, you can input ‘null’ as an optional splitter. Additionally, the third argument enables you to list out column names.

Table.FromList with multiple column without data types or column names

When you provide only a list of column names, Power Query generates a table with unspecified data types in the columns. You can rectify this by defining the data types via a table type.

Table.FromList with single column with data types and column names

To create a table with multiple columns, Table.FromList supports both lists or records within a list. For instance, the formula given below fabricates a two-column table, with no predetermined column names or data types. It’s worth noting that the function splits text values by comma as a default setting.

Table.FromList with multiple columns without data types or column names

You can provide multiple columns by specifying records within a list. To also include data types and column names, you can include the complex table data type.

Table.FromList with multiple columns with data types and column names

If there’s a scenario where your input lacks values, the Table.FromList function fills in with null values. This can happen, for instance, when you submit an empty record or fewer columns for one of the records. The default behaviour here is to display null values, although you can assign a default value in the optional fourth argument.

Table.FromList with Optional Default Values

Note that the column names provided in the records in line 1 of the above example, don’t impact the column names. You determine what column names are returned in the columns argument. The picture shows Name as second column and not Product.

As last example, we arrive at the optional ExtraValues.Type argument. This argument specifies the expected action for extra values in a row that has less columns than expected.

  • ExtraValues.List: combines the last column value with all extra values into a list, storing it in the final column. If you prefer these in a distinct column, it’s advisable to specify an additional column in the columns argument.
  • ExtraValues.Ignore: returns only the columns specified in the column argument.
  • ExtraValue.Error: triggers an error when the splitter argument yields more columns than expected by the table.

Notice how in below example, the first record has 2 columns (Key and Prod) and the second record has three columns (Key, Prod and Id). Below sections show the effect of applying each of the 3 ExtraValues Types.

Table.FromList with

To see all these applications in action, simply copy and paste the below code into the advanced editor.

let
  SingleColumnUndefined = 
  Table.FromList(
    { "Apple", "Prume" }
  ), 
  SingleColumnError = Table.FromList( { 1, 2 } ), 
  SingleColumnNoDataType = 
    Table.FromList(
      { "Apple", "Prume" }, 
      null,         // specifies optional splitter
      { "Product" } // defines column name
    ), 
  SingleColumnDefined = 
    Table.FromList(
      { "Apple", "Prume" }, 
      null, 
      type table [ Product = Text.Type ] // specifies column type and name
    ), 
  MultipleColumnsNoDataType = Table.FromList( { "1, Apple", "2, Prume" } ), 
  MultipleColumnsDefined = 
    Table.FromList(
      { [ ProductKey = 1, Product = "Apple" ], [ ProductKey = 2, Product = "Prume" ] }, 
      Record.FieldValues, 
      type table [ ProductKey = Int64.Type, Name = Text.Type ]
    ), 
  MultipleColumnsExtraValuesError = 
    Table.FromList(
      { [ Key = 1, Prod = "Apple" ], [ Key = 2, Prod = "Prume", Id = "Pr" ] }, 
      Record.FieldValues, 
      type table [ ProductKey = Int64.Type, Product = Text.Type ], 
      null, 
      ExtraValues.Error
    ), 
  MultipleColumnsExtraValuesIgnore = 
    Table.FromList(
      { [ Key = 1, Prod = "Apple"], [Key = 2, Prod = "Prume", Id = "Pr" ] }, 
      Record.FieldValues, 
      type table [ ProductKey = Int64.Type, Product = Text.Type ], 
      null, 
      ExtraValues.Ignore
    ), 
  MultipleColumnsExtraValuesList = 
    Table.FromList(
      { [ Key = 1, Prod = "Apple"], [Key = 2, Prod = "Prume", Id = "Pr" ] }, 
      Record.FieldValues, 
      type table [ ProductKey = Int64.Type, Product = Text.Type ], 
      null, 
      ExtraValues.List
    )
in
  MultipleColumnsExtraValuesList

Learn more about Table.FromList in the following articles:

Other functions related to Table.FromList are:

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

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy