Nested Queries and Slow data refresh

[TonyT2232]

Hi everyone,

Hoping for an assist on an issue I am having with nested tables /records /lists in Power BI.

I am using the Active Directory connector in power BI to obtain members of AD groups. To get the user names for members requires the expansion of the group column and then expanding again to access the group.member column. If you stop there then the query runs in a few minutes.

However to get the next level and obtain the required data from that column another expansion procedure is required to get givenname and distinguishedname or other columns (#"Expanded group.member1") in the example below.

I have read that the slow query is due to "Unfortunately, this method generates an individual call to the second table for every row in the first table."

https://learn.microsoft.com/en-us/power-query/optimize-expanding-table-columns

The article states to break the query up to avoid these individual calls. As a beginner this is where I am getting stuck. I cannot figure out how to get this data without ending up with the individual calls. Can anyone assist with how I would change the below query or get the required information.

I have tried copying the data or doing a Table.ExpandRecordColumn in a seperate query but cannot resolve the calls and slow query.  Each time I try something I get caught having to expand the nested data and slowing everything down (Takes over 12 hrs to run the query)

Thanks for any advice or transform ideas.

When I expand group.member column everything slows down:

As soon as I expand this record and select any of these columns (in bold below) the query slows:

let
 Source = ActiveDirectory.Domains("us.domain.com"),
#"us.domains.com" = Source{[Domain="us.domain.com"]}[#"Object Categories"],

group1 = #"us.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.member1" = Table.ExpandRecordColumn(#"Expanded group.member", "group.member", {"givenName", "distinguishedName"}, {"group.member.givenName", "group.member.distinguishedName"})
in
#"Expanded group.member1"

Thanks,

Tony

[lbendlin]

This is hopeless in Power BI. You get slightly better performance in Powershell. Beware of the recursiveness!!!  Ideally your company provides regular exports from AD for the information you need.