none
Doing a filtered "power query" query on Active Directory RRS feed

  • Question

  • I'm having a quite big AD OU-structure, and would like to do a Excel "Power Query" towards my AD only including some OU's. Anyone that knows how to limit the query to just a few OU's, or to just exclude some OU's. Filtering using RegExp even better, but I don't thing that is possible
    Friday, September 11, 2015 3:55 PM

Answers

  • Haven't worked with AD queries myself, so might not be a good help here.

    But if these long strings all sit in 1 column, you better use a text filter "Doesn't contain":

    = Table.SelectRows(Source, each not Text.Contains([AD], "OU=Test") and not Text.Contains([AD], "OU=Admin"))

    This is also clickable: In the column filter you'll find Text-Filters - last line then is doesn't contain:


    Imke Feldmann TheBIccountant.com

    Sunday, September 13, 2015 5:36 AM
    Moderator

All replies

  • You can either import your data and then apply a column-filter like in Excel, where you check every OU you want to show.

    Or you use a table which keeps the filter criteria (your OU's). To make this table act as a filter, you perform a merge with Join.TypeInner:

    At the moment, Power Query doesn't support Regular Expressions, but you could vote for it.


    Imke Feldmann TheBIccountant.com

    Friday, September 11, 2015 6:56 PM
    Moderator
  • Thanks for the reply, It learned me a lot about M, but I can't get any to work since no row is unique

    I have almost 100'000 rows and the column with OU's comes from the AD property "distinguishedName" and looks like below:
    CN=Name1,OU=Users,OU=1956,DC=win,DC=dom,DC=contoso,DC=com
    CN=Name2,OU=Users,OU=1861,DC=win,DC=dom,DC=contoso,DC=com
    CN=Name3,OU=Users,OU=3485,OU=Test,DC=win,DC=dom,DC=contoso,DC=com
    CN=Name4,OU=Users,OU=1941,DC=win,DC=dom,DC=contoso,DC=com
    CN=Name5,OU=Users,OU=1941,OU=Test,DC=win,DC=dom,DC=contoso,DC=com
    CN=Name6,OU=Users,OU=Admin,DC=win,DC=dom,DC=contoso,DC=com

    No row is unique, they are all different. OK, I can filter out just the OU=xxx parts, but that will still get many different rows to filter out and the examples given in the answer above I only got to work on unique cell-values. Anyway, The best way would be to let the domain controller doing the filtering in the first place.

    My first query rows look like this:

    let
        Source = ActiveDirectory.Domains("win.dom.contoso.com"),
        win.dom.contoso.com = Source{[Domain="win.dom.contoso.com"]}[#"Object Categories"],
        user1 = win.dom.contoso.com{[Category="user"]}[Objects],

    Shouldn't it be possible to add a AD query filter for not seeing the OU=Admin and OU=Test OU's. These OU's will add an extra 100'000 rows to my query result and I don't want that :-)

    Saturday, September 12, 2015 6:07 AM
  • Haven't worked with AD queries myself, so might not be a good help here.

    But if these long strings all sit in 1 column, you better use a text filter "Doesn't contain":

    = Table.SelectRows(Source, each not Text.Contains([AD], "OU=Test") and not Text.Contains([AD], "OU=Admin"))

    This is also clickable: In the column filter you'll find Text-Filters - last line then is doesn't contain:


    Imke Feldmann TheBIccountant.com

    Sunday, September 13, 2015 5:36 AM
    Moderator
  • My request is not to filter the query result, since my problem is that the query result is to big. I would like to limit the query result by adding something like a LDAP query filter already in the query like: "&(objectClass=user)(mailNickName=jeff*)" or something like that
    Wednesday, January 27, 2016 4:25 PM
  • Hello,

    Do you have a solution for this topic? I'm having exactly the same issue as erapade.

    I have an AD of more than 100,000 rows and I want to use power query request to filter by OU before loading data into excel.

    Imke, your solution worked but filter is after the query -> It needs to be in the query.

    Something like (This is not working):

    let
        Source = ActiveDirectory.Domains("DOMAINNAME.com").OU("OU TO SELECT"),
        DOMAINNAME.com = Source{[Domain="DOMAINNAME.com"]}[#"Object Categories"],
        user1 = DOMAINNAME.com{[Category="user"]}[Objects],

    Thanks for your help,

    Friday, July 1, 2016 2:21 AM
  • I know this is an old thread but running into similar issues with AD and Power query.   I have a query source that has the user's Active directory ID but I need to pull in full name and possibly email etc.   Using AD data source for a query seems useless because I can can't prefilter the query.  My AD structures are just too big. Everything errors out when you try to expand records to get to the user information.   Did anyone ever find a workaround?

    Monday, December 11, 2017 3:34 PM
  • I was able to limit the number of rows on a domain, but for some reason I haven't been able to recreate it. I know it can be done, but when trying the same filters on other domains, it still goes to over 100k.

    I would love a concrete answer from someone on how to limit how many rows are in the "refresh" and limit the refresh to a single OU without the "refresh" still including the other OU's in the process...

    Simply filtering the Distinguished Name does not limit the rows in the refresh. 


    Wednesday, August 1, 2018 1:51 PM