Power Query Common Expression. Error

[v-henryk-mstf]

Introduction:
In Power Query, it is very common to encounter various errors. In this article, we are going to show you some common scenarios related to the expression. error for data processing.

Examples:
1. Expression. Error: We cannot apply field access to the type XXX( Date, Number).

Scenario 1: Get a list larger than itself.
The Original table.

Error formula: List.Select( #"Changed Type"[value],each _> [value])

Right formula: let _a=[value] in List.Select( #"Changed Type"[value],each _> _a)

The cause:
[value] cannot be accessed within List, and we could fix this by storing [value] in a variable.
The result as follows:

2. Expression. Error: There weren't enough elements in the enumeration to complete the operation.

Scenario 2: When we pivot the rows with the same name. (here we pivot [Attribute] column which have same name)
The original table:

Pivot column then will occur an error.

The cause:
When the rows with the same value are pivoted, there will be duplicated column names, which is a throwing error.
In this article, the error is understandable, because there are too many elements to
complete the operations.
Solutions:

a) Add [index] column by grouping &expanding it.

= Table.Group(#"Sorted Rows", {"Attribute"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table }})

b) Select [Attribute] column then click pivot column.

c) The result as follows:

3. Expression. Error: We cannot apply operator XXX to types XXX and XXX.

Scenario 3 [value] * 10 to form a new column.
Original table:

Error formula: [value]*10
Right formula: Number.FromText( [value])*10

The cause:
When the multiplication of numbers and numbers is used as the multiplication of text and numbers, that will leads to an error. Therefore, you should convert the text type to a numeric type .

The final show:

Hope this article helps everyone with similar questions.

Author:Yalan Wu

Reviewer: Kerry Wang & Ula Huang