ExtraValues.Ignore

ExtraValues.Ignore (2) 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 ignored.

Examples

The Table.FromList function in Power Query serves as a great example to illustrate the application of the ExtraValues.Ignore enumeration. Its purpose? To transform a list of Records into tables with multiple columns.

To make things clear, let’s imagine a scenario. You have a list with two records. The first one consists of two columns, and the second carries three. Now, if the ‘type table’ argument is programmed to acknowledge only two records, the third column in the second record becomes an ‘extra value’ – an unanticipated element.

Here’s the code in context:

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

Without explicit instructions for managing ExtraValues, the code above encounters an error.

But how does the function react to these ExtraValues?

This is where the optional fifth argument of the Table.FromList function becomes useful. It instructs Power Query what to do with ‘extra values’. If this argument is omitted, the function’s default behavior mirrors that of the ExtraValues.Error enumeration. In other words, it will generate an error upon encountering more column values than expected.

But what if we instead make use ExtraValues.Ignore? When you apply ExtraValues.Ignore, Power Query simply discards any ‘extra values’. They don’t cause any reaction, as demonstrated in the following code:

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
)

In summary, the ExtraValues.Ignore enumeration acts as your failsafe. It helps maintain error-free queries, but it comes with a trade-off – it won’t notify you about inconsistent inputs.

Related articles

Learn more about ExtraValues.Ignore 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

Related enumerations

Other related enumerations are:

• ExtraValues.Error
• ExtraValues.List

Applies to

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

• Csv.Document
• Table.FromList
• Table.SplitColumn

Contribute » | Contributors: Rick de Groot