This article is part of a series on Using Power Query For Excel as an Azure AD Dashboard. As its the first article besides the introduction it assumes zero knowledge of Power Query. The screenshots in this article were taking using Excel 2016 on Windows 10 with an Office 365 Business subscription. If something doesn’t work quite right on another version of Excel, leave a note in the comments.

Goals of this article

We are going to accomplish the following:

Install Power Query if we are running an older version of Excel

if we are running an older version of Excel Identify our domain’s tenant id via both the Azure Portal and AzureRM

via both the Azure Portal and AzureRM Create a basic OData query

Hide some superfluous columns with the Advanced Query Editor. I pick columns that are superfluous to me

Things we will do in future articles

We are going to do a lot in future articles, but these are things you might be chomping at the bit to do.

Render some lists as semi-colon delimited.

Add some new columns to tell us quickly if a row is a Regular user,

B2B user, Local B2C or Federated B2C. This is one thig the Azure Portal

is not completely clear about, and the inspiration for me learning Power

Query.

This is one thig the Azure Portal is not completely clear about, and the inspiration for me learning Power Query. Store configuration data such as Tenant Id in the spreadsheet. In this example we are going to hard code everything. Next article I will (while assuming very little Excel knowledge) show you how to configure this.

In this example we are going to hard code everything. Next article I will (while assuming very little Excel knowledge) show you how to configure this. Break out sub tables such as the group membership. While some of the data in the feed gets rendered as Lists, and can easily be transformed into semi-colon rendered lists, Tables are a little more difficult.

While some of the data in the feed gets rendered as Lists, and can easily be transformed into semi-colon rendered lists, Tables are a little more difficult. Allow editing functionality. We might use VBA-Web to do that in a future article.

Installing Power Query

If you have Office 2016 Power Query is built in. If you have office 2010 or 2013, you can download the add-in right here.

Identifying Our TenantId

Every Azure AD Domain has a Guid called a TenantId associated with it. On that note, everything about Azure has a Guid or two associated with it. I’m going to show you two ways to get that tenanted.

The Azure portal

Log into https://portal.azure.com. Using the leftmost navigation column or the Search button up top navigate to Azure Ad.

From the second most left column select Properties. You should no see amongst other things the tenants Tenant Id, which is labeled as Directory Id. There is a little clipboard icon to let you copy it to the clipboard.

Via Powershell

You should have the AzureRM module already installed. Open PowerShell and type Connect-AzureRM. Enter your azure credentials in the resulting dialog and then copy the tenanted from the resulting output. If you have multiple tenants, you probably know which tenanted you want.

Now place that in notepad, or a cell in a blank excel document. You’re going to need it.

Creating The OData Query

Create a blank workbook. Navigate to the Data tab of the Excel menu. Select Get Data | From Other Sources | From OData Feed.

In the resulting dialog enter the URL https://graph.windows.net/TENANT_ID/users?api-version=1.6, replacing TENANT_ID with the guid we found before.

At this point you will need to authenticate. My credentials are cached and I don’t know how to invalidate them. You will need to login with an Organization account.

You will then see a preview dialog like so.

You can now hit Load to load to a new worksheet in the workbook, or if you want more control select Load To. Note that you can create a Connection Only, if you only want to use this data in other Power Queries. You can also add this query to the Data Model (something I don’t fully understand at the time of writing this).

After you Hit Load or Load To and Ok, you will finally see your data in an Excel Table. Congratulations, you’ve loaded Azure Ad data into excel!

Hiding Columns

You don’t want to hide the worksheet columns. You want to edit the Power Query and remove columns there. When you created the Query the Queries and Connections panel should have appeared on the right. Right click on the query you just created and hit edit.

Now right click on any column and click on Advanced Editor.

In the resulting editor you should see the following:

let Source = OData.Feed("https://graph.windows.net/TENANT_ID/users", null, [Implementation="2.0", Query=[#"api-version"="1.6"]]), #"Removed Columns" = Table.RemoveColumns(Source,{"objectId"}) in #"Removed Columns"

Lets note a few things here.

Most Excel generated Power Query involved a series of variables defined under let that refine the previous variable

The first variable is called source.

Variables can have spaces in their names but use the Notation #”Variable Name”

The in clause declares the rendered results.

C/C++ style variables apply here // Everything till the end of line /* Multiline comment */



Now lets remove a bunch of columns I personally don’t care about. If you wanted to use Azure AD to make a contact information sheet, this is the exact opposite of the columns you want to omit.

Using the Advanced Editor change the query like so.

let Source = OData.Feed("https://graph.windows.net/TENANT_ID/users", null, [Implementation="2.0", Query=[#"api-version"="1.6"]]), RemovedColumns = Table.RemoveColumns(Source,{ "createdObjects", "department", "employeeId", "facsimileTelephoneNumber", "legalAgeGroupClassification", "jobTitle", "telephoneNumber", "mobile", "givenName", "physicalDeliveryOfficeName", "consentProvidedForMinor", "sipProxyAddress", "streetAddress", "city", "state", "country", "postalCode", "ageGroup", "companyName", "preferredLanguage", "manager", "directReports", /* I assume there is some kind of inheritance reason that things could be memvbers of a User, but I don't want to explore that here */ "members", "transitiveMembers", "owners", "ownedObjects", "appRoleAssignments", "licenseDetails", "oauth2PermissionGrants", "ownedDevices", "registeredDevices" }) in RemovedColumns

I didn’t feel the space in the variable name added anything so I change it to RemovColumns. That’s probably my developer bias.

And now we have the beginings of a useful report.