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 

carlwibe's avatar
carlwibe
Frequent Visitor
1 year ago

Thanks slorin... that's absolutely a good solution based on my question.

 

However, due to the fact that my data base is "large" (approximately 60 months times 7,000 rows), this solution seems to significantly slow down data loading. I accept your solution as "accepted" but will think further... mabye is the best soluton to find a way to do this with a measure instead.