Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danextian
Super User
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"





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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

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!
Blog
Twitter
LinkedIn

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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!
Blog
Twitter
LinkedIn

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.