cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User II
Super User II

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

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

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

 


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

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

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

 


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Super User II
Super User II

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

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?






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Super User I
Super User I

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

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.


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Super User II
Super User II

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

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors