Only part of groups returned from active directory

[JuhaU]

Hi

I am trying to pull all active directory groups to Power BI, but I get only small part of them. I have no problem geting them with powershell so I think this is not an autentication issue. I idea what is going on here.

Br Juha

[lbendlin]

show your M code?

[JuhaU]

Hi

Here it is 

let
Source = ActiveDirectory.Domains("nt.ad.xxx"),
ad.xxx = Source{[Domain="ad.xxx"]}[#"Object Categories"],
group1 = ad.xxx{[Category="group"]}[Objects],
#"Expanded securityPrincipal" = Table.ExpandRecordColumn(group1, "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),
#"Expanded group" = Table.ExpandRecordColumn(#"Expanded securityPrincipal", "group", {"location", "member", "owner"}, {"group.location", "group.member", "group.owner"}),
#"Expanded group.member" = Table.ExpandListColumn(#"Expanded group", "group.member"),
#"Expanded group.member1" = Table.ExpandRecordColumn(#"Expanded group.member", "group.member", {"displayName", "sAMAccountName"}, {"group.member.displayName", "group.member.sAMAccountName"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded group.member1",{"top", "msExchMailStorage", "posixGroup", "msExchIMRecipient", "msExchBaseClass", "msExchCustomAttributes"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"securityPrincipal.sAMAccountName", "GroupSAMLName"}, {"group.member.sAMAccountName", "GroupMemberSAMLName"}})
in
#"Renamed Columns"

[lbendlin]

Do you get a timeout message? How long does the same process take in Powershell?  How many of these groups are large (say, over 1000 members) ?

[JuhaU]

There is no errors.  Most of the groups are small. I think 99% of them are have under 10 members. In powershell it takes about 30s

[lbendlin]

Can you try this version?

let
    Source = ActiveDirectory.Domains("xx.net"),
    xx.net = Source{[Domain="xx.net"]}[#"Object Categories"],
    group1 = xx.net{[Category="group"]}[Objects],
    #"Removed Other Columns" = Table.SelectColumns(group1,{"displayName", "group", "securityPrincipal", "distinguishedName"}),
    #"Expanded securityPrincipal" = Table.ExpandRecordColumn(#"Removed Other Columns", "securityPrincipal", {"sAMAccountName"}, {"securityPrincipal.sAMAccountName"}),
    #"Expanded group" = Table.ExpandRecordColumn(#"Expanded securityPrincipal", "group", {"location", "member", "owner"}, {"group.location", "group.member", "group.owner"}),
    #"Expanded group.member" = Table.ExpandListColumn(#"Expanded group", "group.member"),
    #"Expanded group.member1" = Table.ExpandRecordColumn(#"Expanded group.member", "group.member", {"displayName", "sAMAccountName"}, {"group.member.displayName", "group.member.sAMAccountName"})
in
    #"Expanded group.member1"

[JuhaU]

Hi

It didn't help

BR Juha