Microsoft Power Query is a business intelligence tool available in Excel that allow users to connect data from various sources and clean, transform and reshape data in order to meet our data analysis needs.
Power Query is available as add-ins for Excel 2010 & 2013 (need to download and install). Starting from Excel 2016, it was renamed to “Get & Transform” and appears in the Data Tab – no longer need to perform additional installation.
ACTBuilder team started exploring the use of Power Query when we were designing a bank reconciliation solution (for Finance users). Initially, we developed our solutions using Access but we encountered many challenges that required many workarounds. Furthermore, respective users were not familiar to how Excel works. Hence, we decided to explore for alternatives and finally we concluded Power Query is the best option for this bank reconciliation solution.
Through sharing on Power Query, I hope ASM members can explore one more option for data manipulation during day-to-day work, on top of DCS – since Visual FoxPro is no longer supported by Microsoft. Since this is readily available in the recent versions of Excel, it does not require additional licensing fees to learn and use Power Query.
Special Thanks: Sophia Ch’ng & Sylvia Ng for making this sharing session successful!