Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- let
- Source = ActiveDirectory.Domains(),
- <domain name> = Source{[Domain="YourDomain"]}[#"Object Categories"],
- group = <domain name>{[Category="group"]}[Objects],
- FilteredRows = Table.SelectRows(group, each Text.Contains([distinguishedName], "SomeGroupNameFilter")),
- #"CN=SomeGroupName,OU=SomeOU,OU=All,DC=SomeDC,DC=net" = FilteredRows{[distinguishedName="CN=SomeGroupName,OU=SomeOU,OU=All,DC=SomeDC,DC=net"]}[group],
- member = #"CN=SomeGroupName,OU=SomeOU,OU=All,DC=SomeDC,DC=net"[member],
- TableFromList = Table.FromList(member, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
- #"Expand Column1" = Table.ExpandRecordColumn(TableFromList, "Column1", {"department", "title", "sAMAccountName"}, {"Column1.department", "Column1.title", "Column1.sAMAccountName"})
- in
- #"Expand Column1"
- let
- Source = ActiveDirectory.Domains("`YourDomainHere`"),
- MyDomainName = Source{[Domain="`YourDomainHere`]}[#"Object Categories"],
- group1 = MyDomainName{[Category="group"]}[Objects],
- #"Expanded securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),
- #"Sorted Rows" = Table.Sort(#"Expanded securityPrincipal",{{"securityPrincipal.sAMAccountName", Order.Ascending}}),
- #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"securityPrincipal.sAMAccountName", "displayName", "group", "top", "msExchMailStorage", "posixGroup", "msExchIMRecipient", "msExchBaseClass", "msExchCustomAttributes", "mailRecipient", "distinguishedName"}),
- #"Expanded group" = Table.ExpandRecordColumn(#"Reordered Columns", "group", {"member"}, {"group.member"}),
- fxGroupMember_Count = Table.AddColumn(#"Expanded group", "GroupMember_Count", each List.Count([group.member] as list) as number),
- #"fxCount_Replaced Errors" = Table.ReplaceErrorValues(fxGroupMember_Count, {{"GroupMember_Count", 0}})
- in
- #"fxCount_Replaced Errors"
- let
- Source = ActiveDirectory.Domains("`YourDomainHere`"),
- MyDomainName = Source{[Domain="`YourDomainHere`"]}[#"Object Categories"],
- group = MyDomainName{[Category="group"]}[Objects],
- #"Expanded securityPrincipal" = Table.ExpandRecordColumn(group, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),
- #"Filtered Rows" = Table.SelectRows(#"Expanded securityPrincipal", each [securityPrincipal.sAMAccountName] = paramADGroupName),
- #"Filtered Rows_Group" = #"Filtered Rows"{[securityPrincipal.sAMAccountName= paramADGroupName]}[group],
- MembersList = #"Filtered Rows_Group"[member],
- TableFromList = Table.FromList(MembersList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
- #"Expand Column" = Table.ExpandRecordColumn(TableFromList, "Column1", {paramADGroupName, "displayName", "sAMAccountName", "userPrincipalName", "department"},
- {"GroupName", "MembersDisplayName", "sAMAccountName", "userPrincipleName", "department"}),
- #"Replaced Value" = Table.ReplaceValue(#"Expand Column",null,paramADGroupName,Replacer.ReplaceValue,{"GroupName"}),
- #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"GroupName", Order.Ascending}, {"MembersDisplayName", Order.Ascending}})
- in
- #"Sorted Rows"
Add Comment
Please, Sign In to add comment