Why Power Query?
The kids are off to school and the house is quiet. With a fresh coffee in hand, I can finally take on this challenging Excel sheet full of numbers, backed by Power Query (don't worry, we'll explain later). To me, this is the best moment of my working day. After all, what gives more energy than diving into those meaningless numbers and then turning them into a report from which even a 5-year-old can extract the essence?
This might be a slight exaggeration, but in summum, it is not only possible but also necessary. Many entrepreneurs have experienced it before: they successfully turn a phenomenal idea into reality, market it and build their entire company based on gut feeling. Until the moment when the bank account starts painting a different picture, or when the accountant comes by with disappointing figures. What has actually gone wrong? Right: the financial track was missing. (Some) non-financial professionals unfortunately still underestimate the importance of clear reporting and tracking figures.
Power Query to balance the books
The perception that expensive tools are a must for sound financial management, still reigns. But luckily, this perception is false: even with nothing but the free Power Query tool in Excel and a set of brains, you can build automated dashboards to your liking. But what is Power Query and why is it so interesting?
Anyone who often works in Excel and handles a certain amount of data is most likely familiar with infamous formulas such as VLookup or Index(match), aka the best way to triple-check all formulas and still find errors. But you can do better. Power Query is an ETL-tool (Extract, Transform & Load) that performs all kinds of calculations and transformations. Once it’s set up, you’re done. You can apply Power Query to your recurring reports, whether they are daily, weekly, monthly or annual. Get rid of duplications, never-ending calculations, eternal transpositions, recurring conditional columns and filtering or sorting actions.
Also, the data you can use for Power Query is very diverse. It could be a table in your Excel file, a link to a Google sheet or files on the local or online server, but also a whole set of other possible sources.
1+1=3?
With Power Query, you can do more than just process data. You can also combine different tables with each other (Vlookup/Index(match)). To do so, you simply select the matching columns (two or more) in two tables and Power Query does the rest. In addition, you can also paste datasets with the same columns underneath each other. And no, the columns don't even have to be in the same order. Bye-bye, copy-cut-paste!
Once all the necessary transformations in the dataset are done, you can load it to a table and use it to create magic visualisations, such as dashboards, pivots and reports.
Would you like to know more about Power Query or are you interested in training? Or would you rather leave it to us to set up and manage detailed reports? Ask us anything.
