英文:
Select the maximum day of every month in Power Query in Power BI
问题
我在Power BI中有以下数据集：
我想要为每个月/年仅选择具有最高日期的行（并删除其余行）。
在此示例中，将删除第4至第7行。
您有没有任何方法来解决这个问题？
谢谢，
最好
编辑：月份和年份
英文:
I have the following dataset in power BI :
I'd like to select for every month/year, ONLY the rows with highest day (and delete the rest).
In this example, the rows 4-7 would be deleted
Do you have any idea how to approach this problem ?
Thank you,
Best
Edit : month AND year
答案1
得分: 1
你没有提到它，但你不是也需要检查年份吗？无论如何，一种方法是将日期拆分为年、月、日，找到年/月组合的最大日期，查看是否与该行上的日期匹配，然后进行筛选
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "YearMonth", each Date.Year([Date])*1000+Date.Month([Date])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"YearMonth"}, {{"data", each
let a = List.Max(_[Date]),
#"Added Custom2" = Table.AddColumn(_, "Custom", (x)=>if x[Date]<> a then "XX" else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [Custom] = null)
in #"Filtered Rows"
, type table [Date=nullable date]}}),
#"Expanded Data" = Table.ExpandTableColumn(Table.SelectColumns(#"Grouped Rows",{ "data" }), "data", Table.ColumnNames(#"Changed Type"),Table.ColumnNames(#"Changed Type"))
in #"Expanded Data"
英文:
You dont mention it, but don't you have to check year as well? Anyway, one way is to break it down to year, month, day, find the max day for the year/month combination, see if it matches the day on that row, then filter
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "YearMonth", each Date.Year([Date])*1000+Date.Month([Date])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"YearMonth"}, {{"data", each
let a = List.Max(_[Date]),
#"Added Custom2" = Table.AddColumn(_, "Custom", (x)=>if x[Date]<> a then "XX" else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [Custom] = null)
in #"Filtered Rows"
, type table [Date=nullable date]}}),
#"Expanded Data" = Table.ExpandTableColumn(Table.SelectColumns(#"Grouped Rows",{"data"}), "data", Table.ColumnNames(#"Changed Type"),Table.ColumnNames(#"Changed Type"))
in #"Expanded Data"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论