Forum Discussion
Power Query and Graph API Filtering
I have a power Query using Graph API that returns a list Entra (Azure) ID groups starting with the letters "AZA":
Source = OData.Feed("https://graph.microsoft.com/v1.0/groups/?$filter=startswith(displayName,'AZA')&$select=displayName, description, id", [Authorization = "Bearer " & access_token ], [ExcludedFromCacheKey={"Authorization"}, ODataVersion=4, Implementation="2.0"])
What I would like to do is create a separate table that returns the list of members in those groups based on what group is selected.
For example, the Odata call for group members is:
Source = OData.Feed("https://graph.microsoft.com/v1.0/groups/{id}/members?$select=displayName, isManaged, enrollmentType, enrollmentProfileName, isCompliant, managementType, manufacturer, model, operatingSystemVersion, deviceOwnership", [Authorization = "Bearer " & access_token ], [ExcludedFromCacheKey={"Authorization"}, ODataVersion=4, Implementation="2.0"])
So I need to take the "id" value from the first query, and add it as a variable into the second where "{id}" is. Does that make sense? Is that even possible?
Hi kd8ava,
Thank you for reaching out to the Microsoft fabric community forum.
After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:
Verify API Capabilities: Not all Graph API endpoints support complex $filter queries. Ensure that the endpoint you are calling supports the $filter you are using by checking:
Microsoft Graph REST API v1.0 endpoint reference - Microsoft Graph v1.0 | Microsoft LearnInitial API Call with Basic Filter: If direct filtering is unsuccessful, try using a more general filter (e.g., date or status) to reduce the response size.
Load IDs into a Table in Power Query: Create a table of IDs or keys for which you want to retrieve filtered data.
Loop Through IDs to Make Multiple Calls: Utilize a custom function to iterate through your list of IDs and call the API for each ID individually. This avoids the need for complex filtering in a single call.
Combine Results: After obtaining individual responses, combine them into a single table using Table.Combine.
Also please go through the solved link for more information:
Solved: Combine multiple OData calls based on id's in a ta... - Microsoft Fabric Community
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.Hi kd8ava
Here's how I manage it.
I have a first table that lists all my Entra AD groups, and I have a relationship with another table that contains all the members of the groups (here is the query to get all the members of the groups and to get my 2nd table).let
// Step 1 : Get Token
resource = "https://graph.microsoft.com",
tokenResponse = Json.Document(Web.Contents("https://login.windows.net/",
[
RelativePath = #"Azure AD Tenant ID" & "/oauth2/token",
Content = Text.ToBinary(Uri.BuildQueryString(
[
client_id = #"Azure Application Client ID",
resource = resource,
grant_type = "client_credentials",
client_secret = #"Azure Application Client Secret"
]
)),
Headers = [Accept = "application/json"], ManualStatusHandling = {400}
])),
access_token = tokenResponse[access_token],
// Step 2 : Query MS Graph URL
Source = OData.Feed("https://graph.microsoft.com/beta/groups?$select=id,displayName,members", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ]),
// Step 3 : Transformation operations
RenamedColumns = Table.RenameColumns(Source,{{"id", "_Entra_GroupId"}}),
RemovedColumns = Table.RemoveColumns(RenamedColumns,{"displayName"}),
// Expand the ‘members’ column to retrieve the values
ExpandedMembers = Table.ExpandTableColumn(RemovedColumns, "members", {"id", "displayName"}),
RemovedColumns1 = Table.RemoveColumns(ExpandedMembers,{"displayName"}),
FinalRows = Table.RenameColumns(RemovedColumns1,{{"id", "MemberId"}})in
FinalRows
I hope it will help you.
6 Replies
How robust do you need that? Are you expecting to be able to refresh this in the Power BI service?
Hi kd8ava,
Thank you for reaching out to the Microsoft fabric community forum.
After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:
Verify API Capabilities: Not all Graph API endpoints support complex $filter queries. Ensure that the endpoint you are calling supports the $filter you are using by checking:
Microsoft Graph REST API v1.0 endpoint reference - Microsoft Graph v1.0 | Microsoft LearnInitial API Call with Basic Filter: If direct filtering is unsuccessful, try using a more general filter (e.g., date or status) to reduce the response size.
Load IDs into a Table in Power Query: Create a table of IDs or keys for which you want to retrieve filtered data.
Loop Through IDs to Make Multiple Calls: Utilize a custom function to iterate through your list of IDs and call the API for each ID individually. This avoids the need for complex filtering in a single call.
Combine Results: After obtaining individual responses, combine them into a single table using Table.Combine.
Also please go through the solved link for more information:
Solved: Combine multiple OData calls based on id's in a ta... - Microsoft Fabric Community
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.Hi kd8ava,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi kd8ava,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Hi kd8ava
Here's how I manage it.
I have a first table that lists all my Entra AD groups, and I have a relationship with another table that contains all the members of the groups (here is the query to get all the members of the groups and to get my 2nd table).let
// Step 1 : Get Token
resource = "https://graph.microsoft.com",
tokenResponse = Json.Document(Web.Contents("https://login.windows.net/",
[
RelativePath = #"Azure AD Tenant ID" & "/oauth2/token",
Content = Text.ToBinary(Uri.BuildQueryString(
[
client_id = #"Azure Application Client ID",
resource = resource,
grant_type = "client_credentials",
client_secret = #"Azure Application Client Secret"
]
)),
Headers = [Accept = "application/json"], ManualStatusHandling = {400}
])),
access_token = tokenResponse[access_token],
// Step 2 : Query MS Graph URL
Source = OData.Feed("https://graph.microsoft.com/beta/groups?$select=id,displayName,members", [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ]),
// Step 3 : Transformation operations
RenamedColumns = Table.RenameColumns(Source,{{"id", "_Entra_GroupId"}}),
RemovedColumns = Table.RemoveColumns(RenamedColumns,{"displayName"}),
// Expand the ‘members’ column to retrieve the values
ExpandedMembers = Table.ExpandTableColumn(RemovedColumns, "members", {"id", "displayName"}),
RemovedColumns1 = Table.RemoveColumns(ExpandedMembers,{"displayName"}),
FinalRows = Table.RenameColumns(RemovedColumns1,{{"id", "MemberId"}})in
FinalRows
I hope it will help you.
