Power Query and Active Directory....slow|Power Query|Excel Forum|My Online Training Hub

Hi all,

I'm using Power Query to import a list of users (~2000) from Active Directory (Azure).

I am only keeping the initial displayName column, then the user and OrganizationalPerson tables.
I expand the user table to find the UserAccountControl number
These results are filtered to find active users (512).
I expand the manager table to find the manager of an active user.
I expand the OrganizationalPerson table to find department and office.

If I just sit and watch my results refresh, it seems to take a long time.

I don't appear to be able to run/find the Diagnostics tab in my version of Power Query, so I can't see the actual numbers..

I did split each expansion into it's own query - even worse. Scrapped that idea.

I've rearranged the order in which tables are expanded - this seems to have add the biggest effect in terms of speed to load the list of users. (Currently, it's not in the data model as I'm checking results.) FYI - expand OrganizationalPerson first, then manager
I've consolidated the same functions, where I can.
I've filtered my results as early as I can, I think.

To get the same results via PowerShell, doesn't take as long. Yet, I can't get those who will actually be using the report to run PowerShell every time.

Any other ideas how I can make it quicker.

Active Directory is made up of tables, yet I could not figure out how they are all joined together via IDs?
Which would make it quicker to pull the results needed?

Thanks
A