Forum Discussion

carlwibe's avatar
carlwibe
Frequent Visitor
1 year ago
Solved

Grap balance/value from previous year via Power Query

Hi, A bit desperate so I post a question here as well... Looking for a way to retrieve the closing balance from the previous year but fail due to poor knowledge. For all months in the current y...
Need Help
  • slorin's avatar
    slorin
    1 year ago

    Hi, carlwibe 

     

    let
    Source = YourSource,
    #"December Last Year" = Table.AddColumn(YourSource, "December Last Year", each #date(Date.Year([Date])-1, 12, 1), type date),
    AutoJoin = Table.NestedJoin(#"December Last Year", {"December Last Year"}, #"December Last Year", {"Date"}, "December Last Year.1", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(AutoJoin, "December Last Year.1", {"Balance"}, {"Latest/December Value Last Year"})
    in
    Expand

    Stéphane 

slorin's avatar
slorin
Super User
1 year ago

Hi, carlwibe 

 

let
Source = YourSource,
#"December Last Year" = Table.AddColumn(YourSource, "December Last Year", each #date(Date.Year([Date])-1, 12, 1), type date),
AutoJoin = Table.NestedJoin(#"December Last Year", {"December Last Year"}, #"December Last Year", {"Date"}, "December Last Year.1", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(AutoJoin, "December Last Year.1", {"Balance"}, {"Latest/December Value Last Year"})
in
Expand

Stéphane