Forum Discussion

BayliCapri's avatar
BayliCapri
Regular Visitor
1 year ago
Solved

AD Groups and Members

Good morning,

 

I am trying to get a list of AD groups and their members into PBI. I have ried using the Active Directory connector but cannot make heads or tails of which table out of the many I should pull in. I followed an old post but it was from 2021 and so might be outdated as I still couldn't find what I needed. Is the active directory connector the way to go or should I look into some of the Azure connectors? 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,

    Thanks for the solution ray_aramburo  offered, and i want to offer some more information for user to refer to.

    hello BayliCapri , you can refet to the following code.

     

     

     

    let
    Source = ActiveDirectory.Domains("xxx"),
    #"test1" = Source{[Domain="xxx"]}[#"Object Categories"],
    group1 = #"test1"{[Category="group"]}[Objects],
    #"Expanded securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"sAMAccountName"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded securityPrincipal", each ([sAMAccountName] = "Domain Users" or [sAMAccountName] = "Users"))
in
    #"Filtered Rows"

     

     

     

    And you can refer to the following link.

    Solved: PowerBI to Query AD Group Memberships - Microsoft Fabric Community

    Getting a list of AD Groups and their members using PowerQuery | Matt40k

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

    • BayliCapri's avatar
      BayliCapri
      Regular Visitor
      1 year ago

      I've tried that but there are lists and tables within? I've tried pulling display names out of each but it's still not working even with a column in common.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi,

        Thanks for the solution ray_aramburo  offered, and i want to offer some more information for user to refer to.

        hello BayliCapri , you can refet to the following code.

         

         

         

        let
    Source = ActiveDirectory.Domains("xxx"),
    #"test1" = Source{[Domain="xxx"]}[#"Object Categories"],
    group1 = #"test1"{[Category="group"]}[Objects],
    #"Expanded securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"sAMAccountName"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded securityPrincipal", each ([sAMAccountName] = "Domain Users" or [sAMAccountName] = "Users"))
in
    #"Filtered Rows"

         

         

         

        And you can refer to the following link.

        Solved: PowerBI to Query AD Group Memberships - Microsoft Fabric Community

        Getting a list of AD Groups and their members using PowerQuery | Matt40k

        Best Regards!

        Yolo Zhu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.