Power Query Common Expression. Error


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).

vhenrykmstf_0-1667207816762.png

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

vhenrykmstf_1-1667207823433.png

 

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

 

vhenrykmstf_2-1667207856327.png

 

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

 

vhenrykmstf_3-1667207862759.png

 

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

vhenrykmstf_4-1667207988932.png

 

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

vhenrykmstf_5-1667207996759.png

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

vhenrykmstf_6-1667208008180.png

Pivot column then will occur an error.

vhenrykmstf_7-1667208015089.pngvhenrykmstf_8-1667208027803.png

 

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 }})

 

vhenrykmstf_9-1667208221156.png

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

vhenrykmstf_10-1667208289098.png

c) The result as follows:

vhenrykmstf_11-1667208307055.png

 

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

vhenrykmstf_12-1667208326110.png

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

vhenrykmstf_13-1667208345328.png

 

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

vhenrykmstf_14-1667208371649.pngvhenrykmstf_15-1667208378845.png

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:

vhenrykmstf_16-1667208398574.png

 

Hope this article helps everyone with similar questions.

 

Author:Yalan Wu

Reviewer: Kerry Wang & Ula Huang



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*