October 31, 2018
Hi Mynda, I'm learning in bits and pieces and still trying to wrap my head around the utility of Power Query.
I have a table of sales 'SalesMovements'. And I have table of stockcodes 'InventoryMaster'. I just want to list sales for 2 different periods next to each stockcode and then do some math to calculate weekly averages:
SKU | Prev 8 Mo Sales Weekly Avg | Prev 3 Mo Sales Weekly Avg
I could easily accomplish this by creating two query tables (one for 8 months of sales and one for 3 months of sales by stockcode) and then using SUMIFS in my Inventory sheet over each table. But I really want to utilize PQ and the data model - and especially the extensive Date functions in M - to calculate these totals and populate my Inventory worksheet. I've learned a bit about custom functions, value types, lists, etc, but I can't yet see how it all hangs together in order to find a starting point.
What would you suggest?
July 16, 2010