ExtraValues.Ignore

Updated on

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.

The error thrown by

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
)
ExtraValues.Ignore returns specified values only

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.

Learn more about ExtraValues.Ignore in the following articles:

Other related enumerations are:

Applies to

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

Contribute » | Contributors: Rick de Groot