Forum Discussion
How to add a column with previous week values in Power Query/Power Bi
You may merge the table with itself using Prior Week on left side and Week on right one , like
#"Merged Queries" = Table.NestedJoin( #"Promoted Headers", {"Subscription Code", "Product Category", "Prior Week"}, #"Promoted Headers", {"Subscription Code", "Product Category", "Week"}, "Promoted Headers", JoinKind.LeftOuter ),Just expand Amount after that and apply desired cosmetic.
This formula seems to work. A quick and superficial review tells me that it's producing the result you want.
=FILTER($D$2:$D$111,($A$2:$A$111=A2)*($C$2:$C$111=C2)*($F$2:$F$111=G2),"")
FILTER does require the most recent release of Excel, so if it doesn't work for you that could be the reason.
By the way, you posted only a CSV file. It's necessary to put this into an XLS or, preferably, XLSX format. as I've done with the attached.
mathetes Thanks for the quick response!
How can we achieve this using Power query (M Language)?
Any idea?
How can we achieve this using Power query (M Language)?
Any idea?
No idea. I've not used Power Query yet.
And I'm not sure why you'd need to use it, given that FILTER works just fine. You might be taking a sledge hammer to a task that is readily accomplished with a normal everyday hammer. Or, a "power tool" when an everyday manual tool works easily. Take your pick of analogies.
You may merge the table with itself using Prior Week on left side and Week on right one , like
#"Merged Queries" = Table.NestedJoin( #"Promoted Headers", {"Subscription Code", "Product Category", "Prior Week"}, #"Promoted Headers", {"Subscription Code", "Product Category", "Week"}, "Promoted Headers", JoinKind.LeftOuter ),Just expand Amount after that and apply desired cosmetic.
SergeiBaklan mathetes Thank you both for the solution.
Sithi1984 , glad to help
