This tool is generally referred to as ‘Power Query’ when searching for information online. However, with Excel 2016, the tool is also commonly referred to as ‘Get and Transform’. It can link to external data from many different types of sources into local data model in Excel or display them as tables. It uses the M Language to record each step we make and lets us to modify those steps in way we want.
Power Query is one of the most powerful tools added to Excel. There is lot of potential to save time in repetitive data cleaning and formatting using this. It can be accessed by clicking Data -> Get Data -> Launch Power Query editor.
Connect, Transform, Combine and Share are the sequence of steps to blend data make the data model, using Power Query.
Connect
Power Query can be connected to single or multiple data sources like Excel workbook, databases, feeds and cloud services. Microsoft always updates the new data connections to Power Query, so you should update the App to get them added.
Once you connect to any of the data sources then you can directly load data into excel or edit using Power Query by clicking on the option provided. The below snap shows the number of data connections available in Power Query.
Transform
Power Query lets you remove column, change data type, merge tables etc. Each of these actions is a transformation. The above-mentioned options are available in Query Editor/Transform of the Power Query ribbon. The Query Editor records each step you apply to the data.
Combine
You can combine data from multiple data sources into single data model to get unique view into the data. There is no specific option called combine in Power Query. You can achieve this by writing a query or merge/grouping different tables and make them a single table with a unique reference. We can load the merged data into excel for further analysis.
Share
After applying all the conditions/queries on the loaded data. The result will be the processed information. We can save, share or use it for further analysis. Typically, you can use these reports in excel, power BI or any other BI tools for analysis and visualization.