Refreshing data fails because of dynamic script - nextlink

Andras89 

try this

let
// Constants
accessToken = GetAccessToken(),
baseUrl = "https://graph.microsoft.com/v1.0/devices?$top=100",

// Function to get one page
GetPage = (url as text) as record =>
let
response = Json.Document(Web.Contents(
"https://graph.microsoft.com",
[
RelativePath = Text.AfterDelimiter(url, "https://graph.microsoft.com/"),
Headers = [Authorization = "Bearer " & accessToken]
])),
data = response[value],
nextLink = try response[#"@odata.nextLink"] otherwise null
in
[Data = data, Next = nextLink],

// Use List.Generate to loop through all pages
AllPages = List.Generate(
() => [Result = GetPage(baseUrl)],
each [Result][Next] <> null,
each [Result = GetPage([Result][Next])],
each [Result][Data]
),

// Flatten all pages into a single list
AllDevices = List.Combine(AllPages),

// Convert to table
DevicesTable = Table.FromList(AllDevices, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Records" = Table.ExpandRecordColumn(DevicesTable, "Column1", {"id", "displayName", "operatingSystem", "isCompliant", "deviceId", "isManaged"})

Why This Works:

• It uses List.Generate (loop-based, not recursion).

• It constructs URLs with RelativePath only — static from Power BI’s point of view.

• It supports @odata.nextLink within the gateway environment.

It avoids anonymous queries, dynamic hosts, or runtime-injected full URLs.

Additional Notes:

• Make sure your Graph API App Registration has the proper permissions (e.g., Device.Read.All).

• If you get a 403 Forbidden or 401 Unauthorized, double-check token scopes or that you're querying the correct endpoint (/devices, /users, etc.).

If @odata.nextLink includes a full path (like https://graph.microsoft.com/...), make sure to strip it down using Text.AfterDelimiter() as shown.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Hi Andras89,

Thank you for the update, and I’m sorry to hear the solution didn’t resolve the issue. To help get this sorted out, could you share a bit more detail about what’s happening? For example:

• What specific error message do you see in Power BI Service when the refresh fails?
• Does the script work in Power BI Desktop, or are you seeing issues there too?
• Are you using a gateway, and if so, have the data sources been configured in Power BI Service?

These details will help us pinpoint the root cause. In the meantime, I’ve reviewed johnbasha33’s script and suggest a few tweaks to enhance error handling, ensure robust pagination for @odata.nextLink, and support your cloud-only device filtering (trustType = "AzureAD"). The updated approach keeps the base URL static using RelativePath, adds diagnostics to verify data retrieval, and includes checks for API or authentication errors.

Please share the error message or behavior you’re seeing, and we’ll work together to resolve this. Thank you for your patience.