ExtraValues.List - Enumeration

ExtraValues.List (0) is an enumeration that specifies the expected action for extra values in a row that contains columns more than expected. It is a member of the ExtraValues.Type and indicates that extra columns should be collected into a list.

Examples

Let’s have a look at the Table.FromList function in Power Query to demonstrate the implementation of the ExtraValues.List enumeration. The goal of the function? To convert a list of Records into multi-column tables.

Let’s delve into an example to illustrate its usage better. Consider having a list with two records – the first with two columns, the second carrying three. Suppose the ‘type table’ argument is set to accept only two records. In this scenario, the third column in the second record is treated as an ‘extra value’, not expected.

Let’s observe the code in action:

Table.FromList(
      { 
        [ Key = 1, Prod = "Apple"], 
        [ Key = 2, Prod = "Prume", Id = "Pr" ] 
      }, 
      Record.FieldValues, 
      type table [ ProductKey = Int64.Type, Product = Text.Type ],
      null
)

If you do not provide specific instructions for handling ExtraValues, the code above will lead to an error.

The Table.FromList function’s fifth optional argument decides how Power Query treats these ‘extra values’. If this argument is left out, the function defaults to the behavior of the ExtraValues.Error enumeration – returning an error when more column values appear than initially anticipated.

Let’s examine the effect when we deploy ExtraValues.List. In this situation, Power Query takes a different approach – it combines the last column’s value with any ‘extra values’ into a singular list. This consolidated list is then stored in the final column defined in the ‘type table’ argument. The following code showcases how ExtraValues.List retains all surplus data while maintaining the table’s structure integrity:

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
)

ExtraValues.List stores extra column values as list in the final column

In a nutshell, the ExtraValues.List enumeration preserves your column set-up. It captures and maintains all additional data without disrupting the original table structure. However, if you want to store the ‘extra values’ in a separate column, remember to add an extra column in the ‘type table’ argument. That holds the extra values more neatly.

Learn more about ExtraValues.List in the following articles:

An In-Depth Look At The Csv.Document M Function
The article presents a comprehensive examination of the Csv.Document M function. Topics covered include parameters such as Source, Delimiter, Encoding, Columns, QuoteStyle, CsvStyle, and more. » Read more

Other related enumerations are:

Applies to

Here’s a list of functions that work with ExtraValues.Type:

Contribute » | Contributors: Rick de Groot

Examples

Related articles

Related enumerations

Applies to