Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.