For example, we have sales data for the month of January and we build a set of extract and transform routines for the data. The same steps can be rerun by clicking the refresh button and power query will execute all the steps again. Refresh and repeat the steps created: Power query saves the steps used during the extract and transform phase.Databases are extremely fast at transforming data and this feature will make most data manipulations really fast. Please refer to the article for additional details. As of current writing, query folding is available for SQL server database and Odata feed. This means that complicated calculations can be pushed back to the databases. Power query gives the option to push expression to the data source. Can leverage query folding: Second reason to learn power query and a very important one.Load data– Once we have extracted data from multiple data sources, done the required transformation to the data, merged or appended data from multiple data sources, we can load the data to excel.Transform data: Power query has options to pivot columns, unpivot columns, transpose data, group data, split columns by delimiter, convert to uppercase, convert to lowercase, capitalise each word, trim spaces and multiple other data transformation options.Excellent article on Horton works website to connect to HDFS with power query.Article on getting data from Odata feed.Import data from database using native query.Video on loading SQL server data to excel workbook.Article on loading SQL server data to excel workbook.Please refer to the link for loading multiple csv files in the same folder. Loading multiple excel files in a folder does require some additional steps, but it is possible. Connect to a folder- Multiple csv, text and excel files in a folder can be uploaded to excel workbook in one shot.Video on loading csv file using power query. Please refer to this article for loading csv files with power query. Excel and csv file- Load multiple excel and csv files using power query.Web page- You can connect to web pages and extract data from web pages using power query.Extract: Power query can extract data from multiple data sources:.So let us break this and see what power query can do People who are new to the concept of ETL, ETL stands for Extract, transform and load. Become a better data analyst: Power query is ETL for the masses.Considering power query will be such a game changer for data analysts in the future, let us evaluate the top 5 reasons to learn power query. Power query feature is so useful that it has been embedded in the data tab in excel 2016 and is no longer a plugin but base excel functionality now. Power Query is a free add-in in Microsoft excel 2010 and excel 2013.

Excel power query tutorial pdf