Powery Query - return last value from list of dates-CodePudding

I have a query that is indexed with dates. I have a running total that I need to know the last value based on the last row for that date. A picture for context,

If possible, I would like to create a custom column to show the last value for that specified date.

CodePudding user response：

Try

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"

Similarly, for specific person within date

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"

Or if rows are out of order, and you need to sort on index before looking for last row

#"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])