Power Query allows to connect Excel Workbooks to other documents or other data sources. It also allows for data transformations and use of M formulas.
To connect to another data sources select Data tab, Get Data ribbon and then select data source.
Choose a sheet which will get imported into Power Query. Preview is available on the right. There is an option to alter imported data. To perform alterations press Transform Data button.
Example:
To the query above, we want to add another column that would show average customers per site. We don't want original document to be altered where this data is stored and we want this to be calculated automatically every time Excel file that uses Power Query is refreshed.
Select Add Column tab do add a new column where our results will appear. Click Custom Column ribbon.
A window will appear allowing to enter formulas for the newly created column. The formulas are using M language.
To get the average we divide Customers Column by Sites Column. [ ] indicates a column.
After clicking OK, we get the result shown above. Name of the column can be changed by double clicking on it.
Click Close and Load To in the top left corner. The window above will appear asking you how you would like to view your data. We can export as a table, directly into a Pivot Table or a Pivot Chart.
Above is an example if we Loaded our results as a table.
To update query select Data tab and click on Queries & Connections ribbon.
On the right a window will appear showing all established connections. Press refresh button to execute.