Forum Discussion
Unable to run power query on loading more than 100 object IDs
I know I can run this to get the first 100 rows of object IDs
e.g.
let
Source = Json.Document(Web.Contents("https://graph.windows.net/myorganization/users?api-version=1.6")),
But I want more than 100. And this does not work for some reason. Any ideas?
3 Replies
- ImkeF
Community Champion
Hi Anonymous ,
Intellisense shows an error in row 6 because you are using a special sign there.
Escape it like so: Source[#"@odata.nextLink].
But please consider using a different tool for it (Power Automate or the new sp_invoke_external_endpoint stored procedure in Azure SQL Server), as using Graph API isn't recommended nor supported in Power Query:
Lack of Support for Microsoft Graph in Power Query - Power Query | Microsoft LearnHow do I do this?
Bing gave me this code but it errors as well
let
Source = ActiveDirectory.Domains("Domain Name"),
my.domain.com = Source{[Domain="Domain Name"]} [Object Categories],
person1 = my.domain.com{[Category="person"]},
Expanded top1 = Table.ExpandRecordColumn(person1, "top", {"memberOf"}, {"top.memberOf"}),
Expanded top.memberOf2 = Table.ExpandListColumn(Expanded top1, "top.memberOf"),
Expanded top.memberOf3 = Table.ExpandRecordColumn(Expanded top.memberOf2, "top.memberOf", {"displayName", "name"}, {"top.memberOf.displayName", "top.memberOf.name"}),
Filtered Rows = Table.SelectRows(Expanded top.memberOf3, each ([displayName] <> null)),
Removed Columns = Table.RemoveColumns(Filtered Rows, {"person", "distinguishedName"}),
Renamed Columns = Table.RenameColumns(Removed Columns, {{"displayName", "Display Name"}, {"top.memberOf.displayName", "Group Email Address"}, {"top.memberOf.name", "Group Name"}}),
Merged Queries = Table.NestedJoin(Renamed Columns, {"Display Name"}, "AD - User Details", {"Display Name"},"NewColumn",JoinKind.LeftOuter),
Expanded NewColumn = Table.ExpandTableColumn(Merged Queries, "NewColumn", {"User Principal name", "First Name Letter", "Surname Letter"}, {"User Principal name", "First Name Letter", "Surname Letter"})
in
Expanded NewColumn
- ImkeF
I would recommend discussing this with Bing.
