- Monitoring Microsoft 365 with SCOM and the NiCE Active 365 Management Pack - Tue, Feb 7 2023
- SCOM.Addons.MailIn: Monitor anything that can send email with SCOM - Mon, May 25 2020
- Display a user’s logged-on computer in Active Directory Users and Computers (ADUC) - Mon, Jan 21 2019
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.
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.
Want to write for 4sysops? We are looking for new authors.
The article says that Get & Transform can extract data from Active Directory but doesn’t mention how and I can’t see the capability to connect to AD (or any other LDAP source).
Excel 2010 / 2013 with Power Query Plugin:
Power Query (Ribbon menu) > From Other Sources > From Active Directory
Excel 2016 / O365
Data (Ribbon menu) > New Query > From Other Sources > From Active Directory
I’m preparing another post, giving some examples related to Get & Transform with Active Directory.
That’s really weird. I don’t get that option in my Excel 2016.
On the “From Other Sources” submenu, my options are”:
From Microsoft Query
From OData Feed
Nary an entry about Active Directory!
Guess that your version is not current. Microsoft is updating the ‘Get & Transform’ portion of Excel from time to time.
My version is 16.0.6458.2025 and for me it contains:
From Web; From SharePoint List; From OData Feed; From Hadoop File; From Active Directory; From Dynamics CRM Online and FROM Microsoft Exchange
Please try to update to the latest version. – Hope it’ll work for you.
Curiouser and curiouser…
I finally found the Excel version & build number (used to be so easy!). Here’s the tale of the tape:
Mine: 16.0.8326.2107 (32-bit)
So mine would appear to be slightly newer than yours. Are you running 64-bit Excel?
Aha! I may have it.
Are you running the Office Insider builds? I have not been. I’m configuring that now and we’ll see what happens.
Arley, I did a bit research and I found some words which could explain it.
“New features” like that are only added to Office 365 subscriptions. One-time payment licenses like yours only have the features that were included when they were released in Sept 2015.
I also read somewhere that a reinstalling might help:
You could also try to install the Plugin for Excel 2010 / Excel 2013. – I don’t know if this works:
Last thing, please write a post in the technet forum; there are smart guys which might know the answer.
Nope. Still stumped.
I am now running “Version 1707 (Build 8326.2107 Click-to-Run)”.
This is part of my Office365 Business account, is installed locally and has been updated to the most recent monthly release from the Office Insiders program.
But AD is still nowhere to be found.
Ruben, you may be had installed some kind of Add-in, o extention?
I see no tools to connect to AD.
it’s a bit of a sad story. It seems that Microsoft is only providing the AD integration if you subscribed to Office 365 (at least Pro Plus I guess).
If you have it, please try to update to the latest build.
I am not aware of dependencies, but I have usually the RSAT (Remote Server Administration Tools), which includes Active Directory Users & Computers installed on my machines. Perhaps there is some relation.
Last choice could be using Excel 2010 /2013. There you can use the free plugin which contains the AD part.
Hope it helps
Can you help me with the command to trigger a workbook query. I created the button and the code, I am just missing the line of code to trigger my workbook query with the name("test"). I have the code in VBA but I need the C# one.