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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.