powerbi - 筛选 Power Query 中的列，使其仅包含每年的最后一个日期

Question

谁能建议如何在 Power Query 中过滤此列，使其仅包含每年的最后一个日期？

所以，这应该只包含 3 行：

31/12/2019
31/12/2020
31/03/2021

标签： powerbipowerquery

Answer 1

尝试这个

添加自定义列以拉出年份

= Date.Year([EndDate])

添加自定义列以提取每个匹配年份的最大日期

= (i)=>List.Max(Table.SelectRows(#"Added Custom" , each [Year]=i[Year]) [EndDate])

添加自定义列以检查两个日期

 = if [EndDate]=[MaxDate] then "keep" else "remove"

过滤该列

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EndDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([EndDate])),
#"Added Custom2" = Table.AddColumn(#"Added Custom","MaxDate",(i)=>List.Max(Table.SelectRows(#"Added Custom" , each [Year]=i[Year]) [EndDate]), type date ),
#"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom", each if [EndDate]=[MaxDate] then "keep" else "remove"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = "keep"))
in  #"Filtered Rows"

~ ~ ~

对于较大的列表，另一种方法可能更好

添加自定义列以拉出年份

= Date.Year([EndDate])

按年分组并取 EndDate 列的最大值

使用左外连接和过滤器将其合并回原始数据

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EndDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([EndDate])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Year"}, {{"MaxDate", each List.Max([EndDate]), type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"EndDate"},  #"Grouped Rows"  ,{"MaxDate"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"MaxDate"}, {"MaxDate"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([MaxDate] <> null))
in  #"Filtered Rows"