locked
AD query -- trouble with group.member list RRS feed

  • Question

  • Hello,

    I'm attempting to create an AD query that filters for certain groups and all the members of those groups. I start with the groups table and filter to get the groups of interest. No problems so far.

    When I expand the group column to pick up the "member" column, I get group.member and the cell values have changed from "Record" to "List."  When I expand "List" I can tell I'm at the right level of granularity, because the number of rows has increased, and the group names are repeated for the number of members in a group. The problem is that the group.member field now shows "Record" again. I can't expand on it. And when I click on any of the "Record" links, I can see the individual's name.

    Any ideas on how I'm to get the name to show up in the full result set rather than needing to click on the "Record" link? Searching for "Power Query" with "record" has been...unhelpful (expectedly so).

    Thanks

    Saturday, February 7, 2015 12:04 AM

Answers

  • You are right. It is not very clear.

    Assuming you reach into a stage where your first column has the display name of your group and the second column has the records, this screenshot can guide you through the next steps.

    The result will contain two columns. The first column with the group display name on each cell, and the second column with the members of the group.



    Saturday, February 7, 2015 4:23 PM
  • In certain cases, the PQ user interface will pull on a few row in order to determine what fields exist in records of a column. In this case, it appears like it's not picking up any types. I'll investigate to see if this specific case is a known limitation or a bug.

    In the team time, you can get unblocked by manually writing the M that'll expand a the desired field into a column with the following step:

    = Table.ExpandRecordColumn(#"Removed Columns", "group.member", {"displayName"}, {"displayName2"})

    Tristan


    Tuesday, February 10, 2015 6:51 PM

All replies

  • You are right. It is not very clear.

    Assuming you reach into a stage where your first column has the display name of your group and the second column has the records, this screenshot can guide you through the next steps.

    The result will contain two columns. The first column with the group display name on each cell, and the second column with the members of the group.



    Saturday, February 7, 2015 4:23 PM
  • Gil,

    Thanks for the reply. I notice that in my query, displayName is null for all the records returned. I have been able to get the group name information from the sAMAccountName field, although I wonder if that is what is causing the issue when I attempt to expand. 

    Here are more details on my query stages:

    The first stage is with the following query (I left the "GL" filter visible since by itself it's not confidential):

    let
        Source = ActiveDirectory.Domains(<domain>),
        <domain> = Source{[Domain=<domain>]}[#"Object Categories"],
        group1 = <domain>{[Category="group"]}[Objects],
        #"Expand securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),
        #"Filtered Rows" = Table.SelectRows(#"Expand securityPrincipal", each Text.StartsWith([securityPrincipal.sAMAccountName], "GL"))
    in
        #"Filtered Rows"

    The second stage (1st screenshot) is immediately after I expand the group column and select "member." I show the bottom view, too, which shows the three records associated with this list (the correct number of users in that AD group).

    When I "expand" group.member, it changes from List to Record and there is now one row for each user in a group. However, when I go to pick the displayName column from group.member, it gives me the message "No columns were found." Yet, when I select the group.member value, I can see in the view below that there is a displayName. (See 2nd screenshot.) Note that I filtered the records in the third screenshot to just a single group -- 3 records.

    Does this additional information help isolate what I might be experiencing?

    Here is the query to match the final screenshot:

    let
        Source = ActiveDirectory.Domains(<domain>),
        <domain> = Source{[Domain=<domain>]}[#"Object Categories"],
        group1 = <domain>{[Category="group"]}[Objects],
        #"Expand securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),
        #"Filtered Rows" = Table.SelectRows(#"Expand securityPrincipal", each Text.StartsWith([securityPrincipal.sAMAccountName], "GL")),
        #"Expand group" = Table.ExpandRecordColumn(#"Filtered Rows", "group", {"member"}, {"group.member"}),
        #"Expand group.member" = Table.ExpandListColumn(#"Expand group", "group.member"),
        #"Expand top" = Table.ExpandRecordColumn(#"Expand group.member", "top", {"name"}, {"top.name"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expand top",{"msExchIMRecipient", "ciscoEcsbuUMAttributes", "posixGroup", "top.name", "msExchBaseClass", "msExchCustomAttributes", "mailRecipient"})
    in
        #"Removed Columns"

     


    Scott C.

    Tuesday, February 10, 2015 12:20 AM
  • In certain cases, the PQ user interface will pull on a few row in order to determine what fields exist in records of a column. In this case, it appears like it's not picking up any types. I'll investigate to see if this specific case is a known limitation or a bug.

    In the team time, you can get unblocked by manually writing the M that'll expand a the desired field into a column with the following step:

    = Table.ExpandRecordColumn(#"Removed Columns", "group.member", {"displayName"}, {"displayName2"})

    Tristan


    Tuesday, February 10, 2015 6:51 PM