Forum Discussion
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 year, I want to put the previous year's closing balance in a new column, for example named "Latest/December Value Last Year". Attaching a picture where columns A, B and C are my "report data" and column E is the output I would like to have in my Power Query.
Is there a "simple" and understandable way to solve this in Power Query and M?
Thanks in advance!
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
ExpandStéphane
2 Replies
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
ExpandStéphane
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.
