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
I've got it down to ~30 - 45 seconds.
Which if you are only looking at the screen, seems to be a very long time.
The key appears to be the order I expand AD tables in, as noted above.
Thanks
A