Power Query is Easier to Learn than VBA Macros
February 15, 2023 - by Bill Jelen
Problem: I could never figure out the macro recorder or VBA macros. Is Power Query just another macro recorder?
Strategy: While the learning curve for VBA macros is several months to 2 years, you can become very proficient in Power Query in two hours. Buy the book “M is for (Data) Monkey” by Ken Puls and Miguel Escobar.
Additional Details: As you perform Data Cleansing steps, Power Query is writing those steps in a programming language called “M”. While a few people have learned M and will write their queries from scratch, 99.9% of the time you don’t ever have to look at or edit the M code. Contrast this to the VBA Macro Recorder where 98% of macros require knowledge of VBA to edit the macro to make it work.
Problem: I am importing data from a database. By the end of the year, I will have 1.5 million rows and it won’t fit in Excel.
Strategy: Use Power Query to remove any columns or rows that you don’t need. When you are done editing the query in Power Query, open the Close & Load dropdown and choose Close & Load to Data Model. The 1.5 million rows will be loaded behind the scenes. You can then summarize the data using a pivot table.
Alternate Strategy: Tools in Power Query such as Group By will allow you to process the 1.5 million rows and consolidate rows. If you only need to have one row per month per item, you can group by month and item, summarizing quantity and revenue. Consolidating the data might allow it to fit in the Excel workbook.
This article is an excerpt from Power Excel With MrExcel
