Power Query and Graph API Filtering

[kd8ava]

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?

[v-kpoloju-msft · Accepted solution]

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 Learn

Initial 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.

[Jerome_C63 · Accepted solution]

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.

[v-kpoloju-msft · Accepted solution]

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 Learn

Initial 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.

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

Hi @kd8ava,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[lbendlin]

How robust do you need that? Are you expecting to be able to refresh this in the Power BI service?