Graph API Error after 18 months of working flawless

[Dribblej]

Hello everyone,

For many many months I have had an azure logic app created, with a power automate flow, that would allow me to query the graph api for users on my microsoft tenant. I now recieve an error every time the data attemps to refresh. The flow runs on test in power automate, but it will not complete. It even brings the payload of data back when you look at the output but it times out. 

Does anyone have any ideas? I have tried numerous things including renewing the secret and changing it out, changing the pagination with the flow config and it still doesnt work. Also the graph explorer works great when I query directly, https://graph.microsoft.com/beta/users.

Figure 1: Flow set up and works for many months from custom azure logic app into power query in power bi.

Figure 2a: When I run a test in power automate based on a run prior attempted automatically it runs. 

2b.

Figure 3: When I run an actual query and watch it, it fails through timeout error. 

 Figure 4. Power query slightly redacted

let
    url = "https://prod-46.westus.logic.azure.com:443/workflows/REDACTED/triggers/manual/paths/invoke?api-version=2016-06-01&sp=REDACTED",
    body  = "{}",
    Parsed_JSON = Json.Document(body),
    BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
    Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body)])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Value] <> "https://graph.microsoft.com/beta/$metadata#users" and [Value] <> "https://graph.microsoft.com/beta/users?$skiptoken=REDACTED")),
    #"Expanded Value" = Table.ExpandListColumn(#"Filtered Rows", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"accountEnabled", "id", "createdDateTime", "companyName", "department", "displayName", "employeeId", "givenName", "jobTitle", "mail", "surname", "userPrincipalName", "assignedLicenses", "assignedPlans"}, {"Value.accountEnabled", "Value.id", "Value.createdDateTime", "Value.companyName", "Value.department", "Value.displayName", "Value.employeeId", "Value.givenName", "Value.jobTitle", "Value.mail", "Value.surname", "Value.userPrincipalName", "Value.assignedLicenses", "Value.assignedPlans"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Value1",{{"Value.assignedLicenses", "Assigned Lisences"}, {"Value.assignedPlans", "Assigned Plans"}, {"Value.companyName", "Company Name"}, {"Value.createdDateTime", "Created On Date"}, {"Value.department", "Department"}, {"Value.displayName", "Display Name"}, {"Value.employeeId", "Employee Id"}, {"Value.givenName", "Given Name"}, {"Value.id", "Id"}, {"Value.jobTitle", "Job Title"}, {"Value.mail", "Mail]"}, {"Value.surname", "Surname"}, {"Value.userPrincipalName", "UPN"}, {"Value.accountEnabled", "Account Enabled"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Account Enabled", type logical}, {"Created On Date", type datetime}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "date_only", each Date.From([Created On Date]), type date),
    #"Added Custom" = Table.AddColumn(#"Inserted Date", "ct", each 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"ct", Int64.Type}, {"Account Enabled", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","false","Disabled ",Replacer.ReplaceText,{"Account Enabled"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","true","Enabled",Replacer.ReplaceText,{"Account Enabled"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,"-",Replacer.ReplaceValue,{"Job Title"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,"-",Replacer.ReplaceValue,{"Employee Id"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value3", "EmployeeType", each if Text.Contains([Employee Id], "EMP") then "Employee" else if [Job Title] = "Contractor" then "Contractor" else "-")
in
    #"Added Conditional Column"

Im a full time power bi dev for 3 years now, this one has me stumped. Ive tried many things. Any help from the OG's would be much appreciated. Thank you legends.

[Dribblej · Accepted solution]

For anybody who runs into 504 timeout calling graph api through this method into power bi here is what I did to fix the issue:

- filtered query on GET request to only use the fields I needed along with a true filter statement
- Its still odd to me that for roughly 2000 users I was hitting a timeout issue. 
- The Fix I employed gets down to about a 80 second query time, just below the 100 second timeout threshold. 
- Using graph explored this data takes 2 seconds to grab. 
- Im not sure why it takes so long using the HTTP GET request with power automate. 

If you have any solutions to speed up these type of queries into the graph api using power automate and an azure logic app please leave a comment for all to learn from. 

Cheers

[Dribblej]

bump