Get Previous Year data

[subhash_mn]

Hello, I am looking for formula that can get me previous year data from the same table. Below is a example table

I need in PowerQuery

[tackytechtom · Accepted solution]

Hi @subhash_mn ,

Here a solution:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcxBCgAgCETRu7huMSpBnSW8/zXKLNDVh8foWsTUSMDzhEHWvghcehYPjyxyc0XSFQrFtpBHH2l6jmqxdrMN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Year = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NextYear", each [Year] + 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Item", "Year"}, #"Added Custom", {"Item", "NextYear"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Item", "Year", "Value", "NextYear"}, {"Added Custom.Item", "Added Custom.Year", "Added Custom.Value", "Added Custom.NextYear"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"NextYear", "Added Custom.Item", "Added Custom.Year", "Added Custom.NextYear"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added Custom.Value", "PriorYr"}})
in
    #"Renamed Columns"

Note, my table is called table13.

Let me know if this solved your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[subhash_mn]

Thanks Tom!!! Appreciate the help