If we think about it, more or less every task that a Finance Team performs is repetitive, by which we mean that it will be performed every year, every quarter, month, week or even day. That includes:
Get round the Excel data limit – Excel has a row limit of 1,048,576 rows. Any datasets that are larger than this can’t be stored in a single Excel spreadsheet and that causes problems for trying to do any sort of work in pivot tables. PowerQuery is built to deal with millions of rows and to combine datasets from multiple tabs and spreadsheets together. The resulting report may be more than the 1,048,576 row Excel limit, but PowerQuery can load the information straight into a Pivot table, bypassing the Excel row limit.
Quick performance – Have you ever sat for an hour waiting for a large Excel file to open (and then worry whether you’ll be able to do any work in it before it crashes?) Because PowerQuery is built to deal with millions of rows, even large files with lots of calculations can be easily replicated in PowerQuery and will open in seconds. Additional calculations are also quick to perform making the file much more reliable to use at crunch times like period end.