Excel query - looking up email in corp directory

[vaud]

OK, medium skilled excel user (pivots, etc)

Problem:

• We have a subscription to a data service
• I want to assess who (what role) in the business is using the service (by category)
• I have an excel of the last years usage including the correctly formatted email address of the users, but I don't have their role in the excel data provided (and the supplier doesn't capture that)
• Our corp directory (O365) has more data (business unit, etc)

So... is it possible to use the email address to lookup in the data from Outlook to ingest other fields from their contact data? Or is there an easy way to download the whole LDAP (without freaking out corporate IT)

Before anyone asks, I can't ask them manually, I'm looking at 2000 users...

[paulrockliffe]

In Power Query you can connect to Active Directory. It's a Database structure and not the most friendly to navigate as it's 90% meta-data, 10% useful stuff, but you can ultimately get to all your users and whatever is held on AD about them.

[vaud]

Sadly not appearing as a data source for me...

[paulrockliffe]

The M code to connect to the Source is "= ActiveDirectory.Domains("YourDomain")

If you put that in a Blank Query it might work whether you have the option to select it as a Source or not.

If it doesn't work, do you have access to app.powerbi.com? If so that might be a better route.

[vaud]

Sadly neither available, must be blocked.

[simon_harris]

Can you do an ldap lookup directly from excel?