CODEWITHSUNDEEP
exceldaxpowerquery
Cordell Jones
Cordell Jones

Reputation: 93

Powery Query - return last value from list of dates

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, enter image description here

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

Upvotes: 0

Views: 443

Answers (1)

horseyride
horseyride

Reputation: 21298

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"

enter image description here

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"

enter image description here

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

You could also group, get last row, then expand

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"

Upvotes: 2

Related Questions