Add custom column that finds last reading

[abdulbasitb]

Note: Need a solution in power query (no VBA).

Please refer to image below. There needs to be a new custom column added like the green one highlighted below, that would automatically find last reading for the relevant ID, even if the columns are not sorted.

 

[peter789]

Does this work for you?

let
    Source = Excel.CurrentWorkbook(){[Name="Readings"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Meter Reading", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Index",{{"ID", Order.Ascending}, {"Meter Reading", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Index2", 1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Meter Reading", "Index", "Index2"}, {"Meter Reading", "Index", "Index2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Meter Reading", Int64.Type}, {"Index", Int64.Type}, {"Index2", Int64.Type}}),
    #"Inserted Addition" = Table.AddColumn(#"Changed Type1", "Addition", each [Index2] + 1, type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Addition",{"ID", "Index2"},#"Inserted Addition",{"ID", "Addition"},"Inserted Addition",JoinKind.LeftOuter),
    #"Expanded Inserted Addition" = Table.ExpandTableColumn(#"Merged Queries", "Inserted Addition", {"Meter Reading"}, {"Last Reading"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Inserted Addition",{{"Index", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"ID", "Meter Reading", "Last Reading"})
in
    #"Removed Other Columns"

Peter

 

[horseyride]

Another way

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Meter reading", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"ID", Order.Ascending}, {"Meter reading", Order.Ascending}}),
#"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index.1", 0, 1),
Table3 = Table.AddIndexColumn(#"Added Index1", "Index.2", 1, 1),
#"Merged Queries" = Table.NestedJoin(Table3,{"Index.1", "ID"},Table3,{"Index.2", "ID"},"Table3",JoinKind.LeftOuter),
#"Expanded Table3" = Table.ExpandTableColumn(#"Merged Queries", "Table3", {"Meter reading"}, {"Table3.Meter reading"}),
#"Sorted Rows1" = Table.Sort(#"Expanded Table3",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index", "Index.1", "Index.2"})
in #"Removed Columns"

 

[abdulbasitb]

Thanks Peter and horseyride.
Both of the given solutions worked as required.

 

[abdulbasitb]

Does this work for you?

let
    Source = Excel.CurrentWorkbook(){[Name="Readings"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Meter Reading", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Index",{{"ID", Order.Ascending}, {"Meter Reading", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Index2", 1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows",  "Count", {"Meter Reading", "Index", "Index2"}, {"Meter Reading",  "Index", "Index2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded  Count",{{"Meter Reading", Int64.Type}, {"Index", Int64.Type}, {"Index2",  Int64.Type}}),
    #"Inserted Addition" = Table.AddColumn(#"Changed Type1", "Addition", each [Index2] + 1, type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Addition",{"ID",  "Index2"},#"Inserted Addition",{"ID", "Addition"},"Inserted  Addition",JoinKind.LeftOuter),
    #"Expanded Inserted Addition" = Table.ExpandTableColumn(#"Merged  Queries", "Inserted Addition", {"Meter Reading"}, {"Last Reading"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Inserted Addition",{{"Index", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"ID", "Meter Reading", "Last Reading"})
in
    #"Removed Other Columns"

Peter

I was trying to understand how the line below works as it seems handy for other situations also.
Is this command available through ribbon?

#"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Index2", 1,1),type table}})

 

[peter789]

I used the technique described here:

https://www.excelguru.ca/blog/2018/06/27/number-rows-by-group-using-power-query/

Note that Table.Buffer has to be placed around the Table.Sort function to preserve the correct sort order. Horseride's method is simpler and easier to understand. I tried that method first myself but confess I messed the join order up so then went for the more complex solution.

Peter

 

[abdulbasitb]

Thanks