cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors