Forum Discussion
Cannot convert Type to Type List
Hello @RudyWelvaert
you'd have to adjust the syntax to this:
Table. SelectRows (F_SOURCE_DAILY, each each ([FK_DATE] á List. MaxN (F_SOURCE_DAILY[FK_DATE], 5)))
For performance reasons, it would probably make sense to buffer the F_SOURCE_DAILY table before feeding in this step.
Within the 20th argument of Table.Select Rows, the short form [FK_Date] returns the record field (the current row) and not the entire column. Therefore, you must explicitly type the name of the table whose column you want to choose for List.Max.
just a warning: Sort commands in Power Query do not reliably maintain sort order without a buffer: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390
Hello @RudyWelvaert
you'd have to adjust the syntax to this:
Table. SelectRows (F_SOURCE_DAILY, each each ([FK_DATE] á List. MaxN (F_SOURCE_DAILY[FK_DATE], 5)))
For performance reasons, it would probably make sense to buffer the F_SOURCE_DAILY table before feeding in this step.
Within the 20th argument of Table.Select Rows, the short form [FK_Date] returns the record field (the current row) and not the entire column. Therefore, you must explicitly type the name of the table whose column you want to choose for List.Max.
just a warning: Sort commands in Power Query do not reliably maintain sort order without a buffer: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390
Anonymous I took a slightly different approach using List.Contains.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc67CcAwDAXAXVS7kJ5/8izG+68RUCACJQS1V93eJFQIDGYF0ymbEKE+IMugRegOajAiTIdpoBGWwzAQfolfpd+CP2kJqQlBQiQh/CXnAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DateKey = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKey", Int64.Type}}),
#"Keep 5 Days" =
Table.SelectRows(
#"Changed Type",
each
let
varLastDate = List.Max(#"Changed Type"[DateKey]),
varKeepDays = {varLastDate - 4..varLastDate}
in
List.Contains(varKeepDays, [DateKey])
)
in
#"Keep 5 Days"- varLastDate equals 20200820
- varKeepDays returns a list of integers from 20200816 through 20200820
- ListContains is your Select Rows filter condition - it keeps everything in your table from the #Changed Step step that exists in the varKeepDays list.
I think it depends on what you need. The List.MaxN will keep the most recent 5 days in the list, which would be 20, 19, 18, 17, and 15. I don't see a 16 in your data. My method will keep 5 calendar days, so 20, 19, 18, 17, and 16, even though there is no 16 in the data.