Latest posts by Ruben Zimmermann (see all)
- Display a user's logged-on computer in Active Directory Users and Computers (ADUC) - Mon, Jan 21 2019
- Open Windows Admin Center directly from Active Directory Users and Computers (ADUC) - Mon, Dec 10 2018
- Use Polaris to create a RESTful webservice in PowerShell for managing AD users - Wed, Oct 10 2018
In 2013, Microsoft released the Power Query plugin for Excel 2010 and 2013 that enabled users to extract, transform and load the data into a workbook or a model. With its increasing popularity, Microsoft integrated the feature completely into Excel 2016/Office 365 and renamed it Get & Transform, which exactly describes its functionality. The old plugin and the corresponding Excel 2016 component are receiving regular updates.
Performed ETL (extract, transform, load) steps are recorded and stored as a query. You can alter the query through the UI or as raw text, and you can refresh, reuse and transport the query. This is possible because the query is stored in a language called M.
Extracting is done by choosing one of the various offered data sources from different kinds of text files over popular databases (not just Microsoft!), web sites (URLs), OData feeds and other services like Dynamics, Azure, SharePoint, SalesForce, Exchange Online and even Active Directory. As a fallback, ODBC is also supported.
The second step transformation is done in the query editor, a window that usually directly pops up after specifying the data source parameter. From simple formatting to custom operations features all can be found here.
Behind the View tab, the Advanced Editor can be launched. It offers transformation directly with the M code.
I would describe M as a functional scripting language that is near the .net framework.
It is fast for processing big amounts of data and offers functions for text manipulation, dates, basic arithmetic calculations, simple conditions and even some advanced data structures like lists.
An advantage over traditional macros, which are based on Visual Basic for Applications (VBA), is that M is neither influenced by the language of the operating system nor the office package.
The third and last step load is triggered by clicking the Close & Load button. The default action transformed data is finally shown in a new Excel sheet.
When handling big amounts of data, the query result can be also stored in a data model and used for further processing.
Power Query/Get & Transform is one of the new BI tools in Excel. There is Power Pivot, Power Maps and Power View.
Power Query/Get & Transform is also part of Microsoft’s Power BI. It looks and works exactly the same. M queries can be used in both without modifications.
The following text and pictures will briefly demonstrate Power Query/Get & Transform.
A simple text file is the data source. The file content is as follows:
If you’re using Excel 2016, start by choosing the Data tab in the menu ribbon. If you have Excel 2010, you’ll find a new tab name, ‘Power Query’.
The first step is extracting the data by specifying the data source.
- Navigate to the Data tab in the Ribbon menu.
- Click on New Query, Point on From File, and select From Text.
- Select the text file, e.g. SavingsDemo_US.txt, and confirm with Import.
Select the text file to specify the data source.
The Query Editor opens after selecting the file (or any other data source):
Note: The Query Editor window is highly dynamic. By changing the size menu, items get combined and are shown in a slightly different way. A newer version also might change the UI a bit. Don’t worry if my screen looks a bit different than yours.
Looking at the right bottom, the applied steps are shown. These three have already happened.
- The source step reveals the data source that was chosen.
- Promoted Headers meant that the first line of the file is most likely the header and applied it.
- Changed types detected the data type of each column by the best guess, but this needs to be checked, as it’s sometimes not 100% correct.
By clicking the X on the left, the step can be deleted. By clicking on the gear-wheel on the right, the step can be amended.
The data type of the selected column is shown in the top right corner; the small arrow gives the user the chance to change it.
Let’s perform some transformations to see what stands behind them.
Change the Country to UPPERCASE:
- Change to the Transform tab in the ribbon menu.
- Select the Country – Column.
- Click on Format, and choose
The text changes, and a new step is added.
Change is recorded and shown in the Applied Steps section.
As you might have noticed, the performed step is also shown as code in my window.
If you cannot see this line, change to the View tab in the ribbon menu, and check the formula bar.
Ordering by LastName
- Select the "LastName" column, and click on the arrow.
- Choose "Sort Ascending," and confirm with "OK."
Sorting LastName in Ascending order
Sorting is applied, and the new step is added to the list.
Adding custom columns (e-mail address)
- Change to the Add Column tab in the ribbon menu.
- Click on Add Custom Column.
Adding new column in Add Column tab.
- Add "E-Mail" as the column name, and double-click
Double click available columns to create a formula
- Continue the formula with typing & "." &.
- Double click LastName, the formula with & "@mycompany.com."
A green check mark at the bottom left indicates that no syntax errors have been detected.
- Confirm the window OK.
The column is visible, and the step is added.
Change the ordering of the columns
- Drag the "E-Mail" column to the desired place, and drop it there.
The last step is loading the data into Excel.
- Move back to the Home tab of the ribbon menu.
- Click on Close & Load.
The result is shown in the workbook.
The result is shown in a new Excel sheet.
Keep a better overview; give the sheet and query a proper name.
Right-click on the query, and choose Properties …
If you can’t see Workbook Queries, make it visible by changing the Data tab and clicking on "Show Queries."
As a good practice, apply a naming convention, and confirm with OK.
Afterwards, rename the sheet.
Save the Excel workbook.
Refreshing data - verifying the query
- Now, add a new line to the text file, and save your changes.
Add more content to the text file.
- Change back to Excel, and refresh the data:
All the recorded steps were performed!
Editing the query
If you need to modify the query, right-click the query on the left, and choose Edit.
The query editor comes up again, and changes can be either performed in the already-described way, or directly in the code.
Select the "View" tab, and click on Advanced Editor.
Double-click Upper. Change it to Lower, and confirm with OK.
Change to the Home tab, and click on Close & Load.
Changes are applied in the same way.
Within the Advanced Editor, the whole code can be copied onto the clipboard so the query is made transferable.
In my next post I will show you how to extract information from Active Directory with Excel Get & Transform.
Further reading ^
M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query by Ken Puls and Miguel Escobar is a nicely written "cookbook."
"Big Data Analytics with Excel" from Guy Vaccaro contains Power Query and some more interesting stuff.