Doing a filtered "power query" query on Active Directory
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
September 11th, 2015 11:58am

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.

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 2:58pm

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 :-)

September 12th, 2015 2:10am

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:

Free Windows Admin Tool Kit Click here and download it now
September 13th, 2015 1:38am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics