Dangers of distribution lists

Categories: Business Intelligence Power BI Tags: Active Directory PowerQuery

Distribution list are amazing things, they allow us to send mail to our team without having to remember who exactly is in our team, this is especial true in this modern age where we are part of many different teams, sometimes without even realising. These lists can contain hundreds or even thousands of members and the latest NHS IT┬ábug has left IT departments maybe wanting to double-check their distribution lists – hopefully before┬áthe security team comes a knocking.

NHS 850k reply-all email fail: State health service blames Accenture for config cockup < by me https://t.co/TZ1AOqsdKC

— Gareth Corfield (@GazTheJourno) November 15, 2016

Luckily, this is really easy in Excel 2016. Built into Excel 2016 is PowerQuery and one of the out-of-box connectors is Active Directory. With a bit of PowerQuery magic, you can easily pull this data out.

So we want to:

Note: If your unluckily enough to be running an old version of Excel, it possible to do the same thing in Power BI Desktop, which is a free download.

Below is the M code to do this:

For more help on how to enter the M code into Excel see my previous post.

UPDATE: Please note that this only works with Active Directory and doesn’t work with Azure Active Directory. Others have said this is possible via odata or such however I haven’t tried… yet.