The Power Query Formula Language (informally known as “M”) is a powerful mashup query language optimized for building queries that mashup data. It is a functional, case sensitive language similar to F#, which can be used with Power Query in Excel and Power BI Desktop.
A few days ago I got asked to produce a list of users (and their email address) in a number of AD Groups. I already had a SSIS package that had a script task to pull this data from Active Directory and push it into a SQL database and we have a PowerShell script to get the same data in our code repo. After work I set about repeating it but using PowerQuery.
Apart from one quirk the process was pretty straightforward. PowerQuery has built in support for Active Directory. Just click the Data tab, then select New Query > From Other Sources > From Active Directory
This will then pop up with a windows asking for you to select your domain, assuming your machine is joined to the domain and your logged on as a domain user, this should be prefilled in with the correct domain.
The next window just asks you confirm your credentials, you’ll most likely want to use your current windows user.
After you’ve connected to your Active Directory, you’ll be able to navigator, your be able to select your domain from your forest and then select the object you want to query. For this example, select your domain then select group, then click Edit.
This will then open up the Query Editor
This will then list out the groups, or at least a sample. Click on the left\right arrow on securityPrincipal column header, this then bring up a filter window, select sAMAccountName and click on OK. This will give you the friendly name your more then likely to know the groups by.
Now its a question of filtering the list – if you right-click on the sAMAccountName and select Text Filters > Begin With. You can select one of the other options, if you make a mistake or you want to refine it, on the right-hand side, you have Query Settings > Applied Steps, if you click the cog next to Filtered Rows your get
this hopefully doesn’t need any explanation. You can of course filter on other columns if required, such as OU group.
Adding the users involves expanding the member
And the final M script looks like field like we did with securityPrincipal until we get to the individual user object.
The last point is where I hit a few snags. Firstly, I couldn’t expand into the user object, luckily, clicking on Advanced Editor on the Home tab revealed the M code. I’ve included my code below which I hope will help.
I’ve personally found this a useful dive into PowerQuery, hopefully you have too 🙂