Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Proud to be a Super User!
Solved! Go to 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))
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))
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?
Proud to be a Super User!
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.
I'll probably have to keep reading Lars & Imke's psot over and over again. 🙂
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |