none
Power Query Active Directory Groups - Group Size Issue? RRS feed

  • Question

  • Hello -

    I'm trying to create a report in MS Excel that I can just refresh and have it show me a list of what users belong to a specific subset of AD groups.  I want to be able to give this spreadsheet to a key user so that they can have an active, refreshable list of who is assigned to this specific subset of Groups.  I have very little experience with AD, so this may be a rookie question!

    I have connected MS Power Query to Active Directory, and am able to use that to filter the Groups table down to the Members and their user data.

    The issue I have is that there are two groups (my largest two) which populate as if they have no members.  These groups do definitely have members and we can pull reports from other AD tools we have, so I'm confused why Power Query can't seem to pull the member info.  Is there a group size limit or something to which AD just reports null members, or is the data stored in a different way once a group hits a certain size?  Is there a standard user-oriented tool that I can use other than Power Query, if that's not going to work?

    Tuesday, November 25, 2014 1:46 PM

Answers

  • Hi,

    It may be that your account is not allowed to list members in those particular groups, if there is such a thing in AD. Are the other reporting tools running under your account?

    Since those groups are large, you may want to try the memberOfTransitive property, if your company is running domain controllers at the Windows Server 2012 R2 functional level.

    Otherwise, please use Power Query's built-in feedback tool as we would likely need to do some probing in order to understand what's preventing you from listing the users. It could be a bug in PQ, or a limitation of AD that I'm not aware of. We'll reply back to this thread with answers once we get to the bottom it.

    Thanks,

    Tristan

    Wednesday, November 26, 2014 7:32 PM
    Moderator

All replies

  • Hi,

    It may be that your account is not allowed to list members in those particular groups, if there is such a thing in AD. Are the other reporting tools running under your account?

    Since those groups are large, you may want to try the memberOfTransitive property, if your company is running domain controllers at the Windows Server 2012 R2 functional level.

    Otherwise, please use Power Query's built-in feedback tool as we would likely need to do some probing in order to understand what's preventing you from listing the users. It could be a bug in PQ, or a limitation of AD that I'm not aware of. We'll reply back to this thread with answers once we get to the bottom it.

    Thanks,

    Tristan

    Wednesday, November 26, 2014 7:32 PM
    Moderator
  • Any updates or additional information on this issue?

    I'm seeing the same problem (unable to retrieve members for AD group with large number of group members, in my case about 6,500 members).  I've used the built-in feedback tool, but curious if you have suggestions for a workaround or solution.

    memberOfTransitive doesn't seem to be an option for me.

    Wednesday, April 15, 2015 2:11 PM
  • Hi,

    I am still having challenges to pull out large members from large AD groups(>1500 members). Is there any known issue for this? I am able to pull the memebers with vb from the large groups and with Power Query with smaller groups.

    Tuesday, December 29, 2015 8:05 AM
  • I'm having a similar problem to this in Excel 2016 with the Power Query functionality.

    I have a couple larger AD groups (~1000 members each) that won't export.

    I can successfully list them all in Active Directory Users and Computers management console.

    Would love to be able to output these.

    Wednesday, November 30, 2016 6:43 PM
  • Frustrated... Getting the same problem as the rest of the guys with PowerQuery against AD.

    Works fine on small groups, even if they are nested its no problem. Not working at all with larger groups (>1000), also tested with nesting to an empty group = not working...


    Weird this isn't supported in 2016. Is there no way of overriding allowing a maximum of rows? ex. RowLimit(99999)?

    Frustrated and hoping/praying for an answer.

    Tuesday, December 13, 2016 4:12 PM
  • The API is slightly different once you reach 1500 members in the group. We've made the appropriate change, and the fix should reach Power Query in February.
    Tuesday, December 13, 2016 8:50 PM
  • Great! Is there no way to use this fix already now? beta versions or something? 
    Wednesday, December 14, 2016 8:05 PM
  • Curt, as an active user of Power Query with Excel 2013 (on premises), is there a posted roadmap on what functionality is coming to PQ in the short term. I'm not looking to hold you to specific dates or monthly releases, but it would be useful from a planning perspective to have a broad idea on what is coming in the next 6 to 12 months. As a long time user of Excel, it is wonderful to see the innovation in this part of Excel especially for those corporate users that are not on O365 yet. Thanks
    Friday, December 16, 2016 2:55 PM