我有一個用日期索引的查詢。我有一個運行總計，我需要根據該日期的最后一行知道最后一個值。背景關系圖片，
如果可能，我想創建一個自定義列來顯示該指定日期的最后一個值。
uj5u.com熱心網友回復：
嘗試
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}, {"Amount", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type","data",(i)=>Table.Last(Table.SelectRows(#"Changed Type", each [Date]=i[Date]))[Amount])
in #"Added Custom"
同樣，對于日期內的特定人員
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}, {"Amount", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type","data",(i)=>Table.Last(Table.SelectRows(#"Changed Type", each [Person]=i[Person] and [Date]=i[Date]))[Amount])
in #"Added Custom"
或者，如果行亂序，您需要在查找最后一行之前對索引進行排序
#"Added Custom" = Table.AddColumn(#"Changed Type","data",(i)=>Table.Last(Table.Sort(Table.SelectRows(#"Sorted Rows", each [Person]=i[Person] and [Date]=i[Date]),{{"Index", Order.Ascending}}))[Amount])
您還可以分組，獲取最后一行，然后展開
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Date", "Person"}, {
{"expandme", each _, type table},
{"data", each Table.Last(_)[Amount], type number}
}),
#"Expanded" = Table.ExpandTableColumn(#"Grouped Rows", "expandme", {"Index"}, {"Index"})
in #"Expanded"
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/536344.html
標籤：擅长达克斯电源查询