We want query from Power BI to the active directory, asking for users and groups of those users, but the query is very expensive. Poor performance. There is some way to optimize it or get this data in another way.
The query we currently use is:
let
Origen = ActiveDirectory.Domains("domain.local"),
domain.local = Origen{[Domain="domain.local"]}[#"Object Categories"],
user1 = domain.local{[Category="user"]}[Objects],
#"Se expandió top" = Table.ExpandRecordColumn(user1, "top", {"memberOf"}, {"top.memberOf"}),
#"Se expandió top.memberOf" = Table.ExpandListColumn(#"Se expandió top", "top.memberOf"),
#"Se expandió top.memberOf1" = Table.ExpandRecordColumn(#"Se expandió top.memberOf", "top.memberOf", {"cn"}, {"top.memberOf.cn"})
in
#"Se expandió top.memberOf1"
where "domain.local" is our domain.
Thank you