"Expression.Error: We cannot apply field access to the type Date." when doing List.Select

[danextian]

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"

[OwenAuger · Accepted solution]

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

[danextian]

Hi @OwenAuger

Thanks for the response.  Your solution  works like  a charm.

On a  side note, do you happen  to have a link to a documentation relataed  to this?

[OwenAuger]

You're welcome 🙂

Here is a post dealing with a similar problem, though they solve it in a slightly different way:

https://ssbi-blog.de/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power...

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.

[danextian]

I'll probably have to keep reading Lars & Imke's psot over and over again. 🙂