BY LEWIS BISHOP
Excel’s Power Query is awesome. And a lot of people don’t even know they already have it.
I went to a CRM conference recently and spoke to various attendees about their data processing woes. One database officer was explaining to me her issues with a very manual daily process that she does in Excel.
I asked her, “Have you tried using Power Query to automate that?”
She sighed. “We have no budget!”
“Most versions of Excel have it already. Just click on that Data tab and look at Queries…”
Her. Mind. Exploded.
Turns out, every manual step she did could be automated. The different files she had to manually combine could be merged and updated at the push of the Refresh button. The manual transformations – changing the date format in one column, grouping ages into bands in another column, filtering out results here, calculating a percentage there – it could all be done and automated now.
I know we all dream of integrated CRMs and tools that solve all your problems, but that often feels far away. Power Query is an outrageously useful tool that you already have.
So what is Power Query?
It’s a tool that allows you to import, transform and manipulate data from a bunch of different sources. You can pull data from a folder filled with Excel workbooks or csv files. You can pull data from your website. You can embed SQL code into your source and pull data straight from your CRM.
And it’s so user-friendly! It takes a few clicks to select your source, then you can see the data immediately in Excel. You can merge data from different sources and combine them into one table. You don’t need to know how to code or how to write in query language. Just point and click!
Once you’ve pulled that data, you can transform it step by step. These steps are then saved. It’s so transparent! Next time around you just click that oh-so-beautiful Refresh button and voilà! Your tables and tabs are populated. It’s like magic.
Overall, if you’re working with data in Excel to wrangle many datasets together, or to execute a regular manual process, Power Query is your friend. It’s efficient, user-friendly, transparent, and can save you hours of time. And, if you ever think about using a reporting analysis tool like Power BI in the future, Power BI uses Power Query!
Below are some useful links that can show you how to do these things. So please, give Power Query a go! You already have it. What have you got to lose?
How to use Microsoft Power Query - YouTube
Power Query in Excel - Tutorial + Examples (Get & Transform) (excelchamps.com)
