Forum Discussion
AD Groups
Looking to modify the code below. The code itself works great however I dont need the filter (thanks to user amitchandak).
My end result needs to be showing all security user groups, hope this makes sense and that it can be done in the code vs creating a relationship with the user table.
let
Source = ActiveDirectory.Domains("domain.com"),
domain.com = Source{[Domain="domain.com"]}[#"Object Categories"],
group1 = domain.com {[Category="group"]}[Objects],
#"Expanded securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded securityPrincipal", each [securityPrincipal.sAMAccountName] = "SecurityGroupName"),
#"Expanded group" = Table.ExpandRecordColumn(#"Filtered Rows", "group", {"member"}, {"group.member"}),
#"Expanded group.member" = Table.ExpandListColumn(#"Expanded group", "group.member"),
#"Expanded group.member.name" = Table.ExpandRecordColumn(#"Expanded group.member", "group.member", {"name"}, {"name"})
in
#"Expanded group.member.name"
let Source = ActiveDirectory.Domains("domain.com"), domain.com = Source{[Domain="domain.com"]}[#"Object Categories"], group1 = domain.com {[Category="group"]}[Objects], #"Expanded securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}), #"Expanded group" = Table.ExpandRecordColumn(#"Expanded securityPrincipal", "group", {"member"}, {"group.member"}), #"Expanded group.member" = Table.ExpandListColumn(#"Expanded group", "group.member"), #"Expanded group.member.name" = Table.ExpandRecordColumn(#"Expanded group.member", "group.member", {"name"}, {"name"}) in #"Expanded group.member.name"it is not very clear what you are looking for, but let's give it a tr.
2 Replies
let Source = ActiveDirectory.Domains("domain.com"), domain.com = Source{[Domain="domain.com"]}[#"Object Categories"], group1 = domain.com {[Category="group"]}[Objects], #"Expanded securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}), #"Expanded group" = Table.ExpandRecordColumn(#"Expanded securityPrincipal", "group", {"member"}, {"group.member"}), #"Expanded group.member" = Table.ExpandListColumn(#"Expanded group", "group.member"), #"Expanded group.member.name" = Table.ExpandRecordColumn(#"Expanded group.member", "group.member", {"name"}, {"name"}) in #"Expanded group.member.name"it is not very clear what you are looking for, but let's give it a tr.
This is exactly what I was looking for. Thanks and sorry for the lack of detail, credit to you for reading between the lines also : )
