PowerQuery (also known as Get Data and Transform) is a little known part of Excel that in our experience can make a huge difference to the efficiency and accuracy of a Finance Team’s repetitive processes. PowerQuery connects Excel workbooks to datasets (including Excel files), then gathers all the information in those datasets together, and then cleans it and transforms it into a useable shape. PowerQuery remembers the steps of to connect to, clean and transform the data so once you’ve performed the transformation once, you’ll never have to do it again. Excel will take care of it quickly and accurately.

 

If you think about it, almost everything that a Finance Department does is repetitive, and most of it uses data that needs cleaning, transforming, joining with other datasets and so on before it can become useful. Now imagine that you don’t need to do any of that transforming, other than saving information in a certain place and clicking a refresh button. Sound good?

 

Welcome to PowerQuery.