Power Query is a self-service ETL tool for Excel 2013 and Office 365. This tool was previously named Data Explorer. It supports many data sources such as SQL Server, ODBC connections, and most web sources. It has unique features such as fetching data from online searches. There are some data transformation features embedded in this tool that makes ETL very easy to use.
Power Query is not as strong as SSIS; rather, it can be considered as a lighter version of SSIS. Data transformations using this tool are not that mature compared to some SSIS transformations, such as a slowly changing dimension and an OLE DB command. However, it is highly useful for integrating data from sources such as files, database tables, and online data; applying merge or append; creating new columns; or splitting with some other transformations to make the data ready for self-service data analysis.
The result of the data built by Power Query can be used in PowerPivot modeling or directly...