Table of Contents
What is Power Query ?
Power query is a combination of various features of Excel and VBA. It has user friendly interface to perform the day to day functions.
It can not only perform VLOOKUP of Excel but also automate the same by generating a code, on its own, in ‘M’ language. Most of the data transformation and cleansing actions required in day to day business have been made available via buttons/options on the ribbons.
However, if you want to build some complex solution then you must explore ‘M’ language in depth.
Glimpse of Power query features:
- Connect with a wide variety of data sources like databases, txt(26AS), csv, xml, json(GST), pdf, SAP bw etc
- Combine multiple data sets from various folders, files and sheets
- Perform data cleansing (Find and replace in excel)
- Merge the data from multiple files (VLOOKUP in excel)
- Merge the columns in a table (Concatenate in excel)
- Perform mathematical operations i.e sum, average, min, max, etc based on conditions in various columns (Subtotals in excel)
- Unpivot (restructure) the table
How is Power Query useful ?
Power query helps you increase your Work Efficiency and save you from doing the monotonous work of combining the files, updating the reports or sheets at each report cycle. From making simple tables and complex reports in EXCEL by using various functions and features each time to combining the data, performing data transformations and calculations in few clicks, POWER QUERY has got it covered.
Example:
Let’s take the example of commonly faced problem of Updating Excel Files each month having the same structure.
In EXCEL: We usually copy paste the data one below the other in one sheet.
In VBA: We write a Lengthy code to combine multiple files.
In Power Query: The user has to keep all the new files for the month in one folder and click refresh.
Click here to read more about combining multiple excel files using Power Query.
Thus, with the help of Power Query one can easily save time of generating various reports each month and break the Deadly Report Cycle.
Click here to know how to install Power Query in Excel 2013.
Power Query is available by default from Excel 2016 onwards.