Forum Discussion
Excel 2016 how to autofill data entries based on a reference sheet
The workbook you would be building is the one with the pivot tables. The third table (or the same information held within the data model for Power Query) would be there and would be refreshed monthly (all the previously defined steps would be repeated automatically). New data might replace the old or you could choose to append it.
One objective would be to get hold of the sales data from as near the source as you are allowed to go (in the current workbook, in another workbook, within an exported text file, using an SQL query to interrogate a corporate database). All the data manipulation would be within PQ so that you never need to touch the data again. The category and brand data could be read from a separate, largely static file or could be a table in the destination workbook.
p.s. By 'refresh' I simply mean click the 'Refresh All' button on the Data ribbon tab.
p.p.s. I have tried to append a file as the 'starter for one'. It contains the three tables on a single sheet but that would not be the longer-term aim. Open the queries from the panel on the right and step through the edits to see the data at each stage.
Just to make sure I understand your suggestion: Say I complete these steps for all the data I have up until now and end up with a third table (from step 7) to use for analysis and charts. When another month's data is ready to import, I would basically have a new "Sales" table (once again missing Brand and Category fields). I would then need to merge that with the same old Brand table (containing the category and brand fields for each product ID) and then paste this data onto table 3. Correct?
Also, do you suggest I start off with these tables (especially the third table resulting from the merge and the Brand table used as a 'reference') in the same workbook?
The workbook you would be building is the one with the pivot tables. The third table (or the same information held within the data model for Power Query) would be there and would be refreshed monthly (all the previously defined steps would be repeated automatically). New data might replace the old or you could choose to append it.
One objective would be to get hold of the sales data from as near the source as you are allowed to go (in the current workbook, in another workbook, within an exported text file, using an SQL query to interrogate a corporate database). All the data manipulation would be within PQ so that you never need to touch the data again. The category and brand data could be read from a separate, largely static file or could be a table in the destination workbook.
p.s. By 'refresh' I simply mean click the 'Refresh All' button on the Data ribbon tab.
p.p.s. I have tried to append a file as the 'starter for one'. It contains the three tables on a single sheet but that would not be the longer-term aim. Open the queries from the panel on the right and step through the edits to see the data at each stage.
Thank you for the example and great explanation!
Now that I have a better understanding of this process, a few additional questions arise. In the actual merging process the join kind I used was "Left outer (all from first, matching from second)." Oddly, my Sales table has 11'966 rows. Once I close and load the Merge table to a new sheet (without selecting "Add to data model", the resulting table has 12'565 rows loaded. I added another month's data to the Sales table, refreshed the Merge table, and again, more rows appeared in the latter (12'798 vs 13'443) The total sums of Quantity and Value are, naturally, different as well. Any idea as to what the reason might be?
Secondly, I was wondering if it is an issue for me to change the date format in my Merge table (outside PQ, as the format I prefer is "mmm-yy," not available among the PQ date formats) and add an additional column where I copy the Date column but change its format to "yyyy-mm" using a formula (this is exclusively for a slicer's sake later on and is not used in any pivot table).
EDIT: There are not duplicate rows in the Sales table.
1. The left outer issue is a mystery. I agree that you should not finish with more records than there were in the left table, though I am not sure what happens if there is more than one record in the right hand table that matches one of the foreign keys.
2. With dates you need to take care that the representation is what you need
2.2018 [decimal number]
1/2/2018 [date/non-US]
Feb-2018 [text]
Once a proper date is output to Excel, you can apply number formatting and I believe it will persist through refresh.
3. You can add columns within PQ. If you use 'by example' and enter a couple of example outputs PQ will often generate the process instruction for you and fill the column.