Point Excel's Power Query at a folder once, and next month's report rebuilds itself on one Refresh All
The monthly report you rebuild by hand is the one Excel was always willing to rebuild for you.
Writing at MakeUseOf on September 12, Adaeze Uche describes three years of rebuilding one monthly report by hand: opening 114 files from 19 locations, copying the data out, fixing the formatting, stripping unwanted rows, and combining the result. Power Query replaces that with a setup you do once. Under the Data tab, Get Data then From Folder points Excel at the storage location, Combine and Transform Data reads every file in it, and from there you drop columns, correct data types, use Unpivot Other Columns to turn a wide layout into database-friendly vertical rows, and click Close and Load. Next month you drop the new files into the same folder and click Refresh All, which the author reports finishing in seconds.
