Azure Active Directory is a great piece of technology that can do several things around authentication. However, the UI provided by portal.azure.com is lacking, and documentation can be a little unwieldly. This article serves as the first in a series of that will show you how to use Microsoft Power Query (and maybe some VBA) to create a better dashboard for user and application management for Azure AD.
Technologies utilized
In this series we will be using the following technologies.
- Microsoft Excel to host the dashboard. Power BI would also work, but I don’t know Power BI (at least yet).
- Microsoft Power Query to query the graph APIs to get the data we need and present it into Excel Tables
- Windows Azure Graph will be the OData REST API that Power Query will use to
I have the beginnings of a dashboard that solves some problems I’ve encountered during about a year of Azure Development. I still have to figure out a few things to get the dashboard I really want. While I’m pretty good with Excel, I am very new to Power Query. As an advanced Polygot programmer that is new to the Power Query language I struggle to create code elegant reusable code in Power Query due to my ignorance of its syntax. I’ll be learning along with you. This list might grow.
Getting the Dashboard
I’m actually not sure yet. The main artifact of this project will be an Excel Spreadsheet with Power Queries for the Azure Graph APIs, and maybe some VBA to push the data. I’d like to store the Power Query code as text files in GitHub. Solutions for storing VBA as text in version control exist (e.g. RubberDuck VBA).
Also, I need to figure out a reliable way to strip all the data from my tenant from the spreadsheet.
I’m going to also look into distributing this via OneDrive or Office365 SharePoint. I’m actually not sure if I can make public links there.
Why not Microsoft Graph?
The short answer is at the time I started using Azure AD, many things I wanted to do didn’t work yet in the Microsoft Graph. One of the main use cases I will talk about here is B2C users, which as of 2018-08-31 is still not supported by the Microsoft Graph. Support does say “Coming soon.”
The long answer is as follows Certain operations require you to use one of two graph APIs, the old Azure Graph (https://graph.windows.net) or the new Microsoft Graph (https://graph.microsot.com). To clarify that statement, while both graph APIs expose more functionality than the azure portal (http://portal.azure.com), some operations only work with one endpoint. In theory eventually the Microsoft Graph will be a complete superset of the Azure Graph. That hasn’t happened yet.
zippy1981inexilium 
One thought on “Using Power Query For Excel as an Azure AD Dashboard”