cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dskeane
Helper I
Helper I

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"

1 ACCEPTED SOLUTION
Rocco_sprmnt21
Super User II
Super User II

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.

View solution in original post

2 REPLIES 2
Rocco_sprmnt21
Super User II
Super User II

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.

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors