Forum Discussion
Super User"Expression.Error: We cannot apply field access to the type Date." when doing List.Select
Hi All,
In the script below, the custom colmn created in #"Custom 2" is returning an error "Expression.Error: We cannot apply field access to the type Date." What I'am trying to achieve is select from the list in Custom column all dates that are less than or equal to the value in end column.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDRX0lEy1TeGsmN1opWMERLGuCSQdMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [start = _t, end = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"start", type date}, {"end", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let x = Number.Round((Number.From([end]-[start]))/28,0) + 1 in List.Distinct(List.Transform(List.Dates([start], x, #duration(28, 0, 0, 0)), Date.EndOfMonth ) ) ),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Select([Custom], each _ <= [end])),
#"Custom 1" = #"Added Custom1"{0}[Custom.1],
#"Custom 2" = #"Custom 1"{0}
in
#"Custom 2"Hi danextian,
The error seems to be caused in step #"Added Custom1" because [end] cannot be accessed within List.Select (rough explanation).
You can fix this by storing [end] in a variable. Try this and it should work:
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let EndThisRow = [end] in List.Select([Custom], each _ <= EndThisRow))
4 Replies
- OwenAuger
Hi danextian,
The error seems to be caused in step #"Added Custom1" because [end] cannot be accessed within List.Select (rough explanation).
You can fix this by storing [end] in a variable. Try this and it should work:
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let EndThisRow = [end] in List.Select([Custom], each _ <= EndThisRow))
- OwenAuger
You're welcome :)
Here is a post dealing with a similar problem, though they solve it in a slightly different way:
Also you can refer to the Power Query Language Specification:
https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification
It looks like page 85 relates to this topic, but I haven't read it properly.
