Comments

One of the annoying things about working with multiple languages is they each have their differences – obviously, otherwise there would only be 1 language! Although there is good reason to have these differences they still have elements that are a pain. One of the most annoying things, other then “do you require a semicolon at the end of the statement?” is comments. Comments aren’t executed or complied but they help developers read code. Remember, the code is a interpretive language, its designed to be gateway between the machines and humans, if you write something complex your more then likely need to be include more comments explain what it does or look at refactoring so other developers can continue your work.

The few options are:

Double dash (–)

— Single line quote

Single Hash (#)

# Single line quote

Double forward-slash (//)

// string domain = “matt40k.uk”;

All of these are single line quotes, as in each line needs the require comment and the comment continues till the end of the line \ carriage break.

You also got:

Forward-slash and asterisk (/*)

/* Multi-Line
quote */

Forward-slash and asterisk marks (/*) the start of the comment, the comment continues until the asterisk marks and Backward-slash (*/). The code must close any open Forward-slash and asterisk marks (/*) comments in order to compile.

With Forward-slash and asterisk, you can comment in-line, for example

Select * from /* Change the code to the right */ sys.tables

This is valid T-SQL that will return the all the columns for meta data about tables on the selected database

You can also use this across multiple lines, for example

select

TableSchema = schema_name(schema_id)

,TableName = name

/*
You could also add

other tables

 

*/
from

sys.tables

 

Power Query Formula (M) Language

Double dash (–) No
Single hash (#) Yes
Double forward-slash (//) No
Forward-slash and asterisk (/*) Yes

Transact-SQL (TSQL)

Double dash (–) Yes
Single hash (#) No
Double forward-slash (//) No
Forward-slash and asterisk (/*) Yes

C sharp (C#)

Double dash (–) No
Single hash (#) No
Double forward-slash (//) Yes
Forward-slash and asterisk (/*) Yes

 

Fun fact, my most commonly used comment?

To-Do

Getting a list of AD Groups and their members using PowerQuery

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

PowerQuery - Excel > Menu

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.

PowerQuery - Excel > Connection

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.

PowerQuery - Excel > Navigator

This will then open up the Query Editor

PowerQuery - Excel > 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

PowerQuery - Filter

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 🙂