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.
- Hi mathetes
FILTER function is not working for me, Can you please help me with some other approach?Help you with what? You jumped in on this thread "from the side," so to speak, a thread that had ended half a year ago. And you jumped in without any introduction as to what you're doing.
It would be better to start a whole new thread. And I would strongly suggest you consider helping others help you by posting a copy of your spreadsheet, the one where the "FILTER function is not working."
- ok
mathetes Thanks for the quick response!
How can we achieve this using Power query (M Language)?
Any idea?
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.
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.
