Yes, it is time to upskill in Excel to automate time-consuming tasks and boost your efficiency. For that, if you haven’t heard of, or haven’t used, Power Query in Excel, it is the right topic to learn to leverage your skills.
Power Query is a feature that has been introduced in Excel 2016 onwards which aims at automating the ETL process (Extract, Transform, and Load) of relatively large data sets. That is, with Power Query you can record a series of steps to import your data, clean and transform it, then load it with the final shape into an Excel spreadsheet. The same steps are repeated automatically by Power Query once the workbook is refreshed.
A simple scenario that shows how Power Query shines at automation is the process of importing a data sheet of daily sales with revenues recorded in multiple currencies across many offices. All revenues must be converted then reported in US Dollar.
This daily task requires a financial officer to look up the exchange rate for each currency from another sheet then apply the US Dollar conversion formula. Traditionally, one performs this task by looking up the exchange rate using vlookup() then writing a formula to convert each revenue record into USD.
With Power Query you just need to record the following steps once, then refresh the workbook every time a new Sales sheet is received, or Exchange Rates are updated:
- Import the Sales and the Exchange Rates files into a new or existing workbook
- Merge both sheets into one using a common column; here, the Currency Code
- Select the value(s) to return from both sheets
- Create a custom column to calculate the revenue values in US Dollars
- Load final data and keep refreshing whenever source files are updated
It might look intimidating at the beginning, but I am confident that Power Query will be a game-changer once you start using it.
Watch this video that takes you through the above steps in more detail.
