Did you know, power query is free tool available in Excel right from MS Excel 2010 onwards ?
If you do not have Power Query installed in your MS Excel 2010/2013, you can download it from here. From Excel 2016 onwards, it is an inbuilt feature. Power query is a combination of various features of Excel and VBA. It has user friendly interface to perform the day to day functions. Power query 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.
EVERY EXCEL USER
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.
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
Let’s take the example of commonly faced problem of COMBINING MULTIPLE EXCEL FILES 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: You can do this in TWO steps
- Keep all the files which are to be combined in one folder
- Go to Data TabàNew QueryàFrom FolderàProvide the path of the above folder à Click Ok
POWER QUERY PROCESS
If you want to ADD MORE FILES in the combined data table then simply keep the additional files in the folder above and click Refresh All in data tab.
Want to know more about Power query ? Subscribe us on our YouTube channel
