cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TonyT2232
New Member

Nested Queries and Slow data refresh

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:

 

TonyT2232_0-1678789695288.png

 

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

 

TonyT2232_1-1678789842404.png

 


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

1 REPLY 1
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors