In PowerBI, one of my report schedule Refresh is failing with the below reason.

[sravs2007]

In Power BI, One of my report is failing with below reason

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

My Data source use Azure APIs

[sravs2007]

@v-lgarikapat Here is my API , am not using pagination 

let
// Define API parameters
organization = "Org",
project = "Proj",
pat = "MyPat", // Secure this!
encodedPat = "Basic " & Binary.ToText(Text.ToBinary(":" & pat), BinaryEncoding.Base64),

// Fetch test runs directly from API
GetDocument = try Json.Document(Web.Contents("https://vstmr.dev.azure.com/" & organization & "/" & project & "/_apis/testresults/runs?api-version=7.2-preview",
[Headers=[Authorization=encodedPat]])) otherwise null,

// Convert API response to table format
ConvertedTable = if GetDocument <> null and Record.HasFields(GetDocument, "value")
then Table.FromRecords(GetDocument[value])
else Table.FromRows({}, {"id", "isAutomated"}),

// Add API URLs dynamically for each test run
AddUrls = Table.AddColumn(ConvertedTable, "ApiUrl", each
"https://dev.azure.com/" & organization & "/" & project & "/_apis/test/runs/" & Text.From([id]) & "/results?api-version=7.2-preview"),

// Fetch JSON data for each test run & handle errors
AddJsonData = Table.AddColumn(AddUrls, "JsonData", each
try Json.Document(Web.Contents([ApiUrl], [Headers=[Authorization=encodedPat]])) otherwise null),
#"Filtered Rows" = Table.SelectRows(AddJsonData, each ([isAutomated] = false)),
#"Expanded JsonData" = Table.ExpandRecordColumn(#"Filtered Rows", "JsonData", {"count", "value"}, {"JsonData.count", "JsonData.value"}),
#"Expanded JsonData.value" = Table.ExpandListColumn(#"Expanded JsonData", "JsonData.value"),
#"Expanded JsonData.value1" = Table.ExpandRecordColumn(#"Expanded JsonData.value", "JsonData.value", {"id", "project", "startedDate", "completedDate", "testCase", "testPoint", "lastUpdatedDate", "createdDate", "url", "testPlan", "owner", "runBy"}, {"JsonData.value.id", "JsonData.value.project", "JsonData.value.startedDate", "JsonData.value.completedDate", "JsonData.value.testCase", "JsonData.value.testPoint", "JsonData.value.lastUpdatedDate", "JsonData.value.createdDate", "JsonData.value.url", "JsonData.value.testPlan", "JsonData.value.owner", "JsonData.value.runBy"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded JsonData.value1",{{"JsonData.value.id", "TestResultId"}}),
#"Expanded JsonData.value.project" = Table.ExpandRecordColumn(#"Renamed Columns", "JsonData.value.project", {"name"}, {"JsonData.value.project.name"}),
#"Expanded JsonData.value.testCase" = Table.ExpandRecordColumn(#"Expanded JsonData.value.project", "JsonData.value.testCase", {"id", "name"}, {"JsonData.value.testCase.id", "JsonData.value.testCase.name"}),
#"Expanded JsonData.value.owner" = Table.ExpandRecordColumn(#"Expanded JsonData.value.testCase", "JsonData.value.owner", {"displayName"}, {"JsonData.value.owner.displayName"}),
#"Expanded JsonData.value.runBy" = Table.ExpandRecordColumn(#"Expanded JsonData.value.owner", "JsonData.value.runBy", {"displayName"}, {"JsonData.value.runBy.displayName"}),
#"Expanded JsonData.value.testPlan" = Table.ExpandRecordColumn(#"Expanded JsonData.value.runBy", "JsonData.value.testPlan", {"id"}, {"JsonData.value.testPlan.id"}),
#"Expanded JsonData.value.testPoint" = Table.ExpandRecordColumn(#"Expanded JsonData.value.testPlan", "JsonData.value.testPoint", {"id"}, {"JsonData.value.testPoint.id"}),
// Add API URLs dynamically for each test result (attachments)
AddUrls2 = Table.AddColumn(#"Expanded JsonData.value.testPoint", "AttachmentApiUrl", each
"https://vstmr.dev.azure.com/" & organization & "/" & project & "/_apis/testresults/runs/"
& Text.From([id]) & "/results/" & Text.From([TestResultId])
& "/testlog?type=generalAttachment&api-version=7.1-preview.1"),
// Fetch JSON data for test attachments & handle errors
AddJsonData2 = Table.AddColumn(AddUrls2, "AttachmentJsonData", each
try Json.Document(Web.Contents([AttachmentApiUrl], [Headers=[Authorization=encodedPat]])) otherwise null),
#"Expanded AttachmentJsonData" = Table.ExpandRecordColumn(AddJsonData2, "AttachmentJsonData", {"value", "count"}, {"AttachmentJsonData.value", "AttachmentJsonData.count"}),
#"Expanded AttachmentJsonData.value" = Table.ExpandListColumn(#"Expanded AttachmentJsonData", "AttachmentJsonData.value"),
#"Expanded AttachmentJsonData.value1" = Table.ExpandRecordColumn(#"Expanded AttachmentJsonData.value", "AttachmentJsonData.value", {"logReference"}, {"AttachmentJsonData.value.logReference"}),
#"Expanded AttachmentJsonData.value.logReference" = Table.ExpandRecordColumn(#"Expanded AttachmentJsonData.value1", "AttachmentJsonData.value.logReference", {"filePath"}, {"AttachmentJsonData.value.logReference.filePath"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded AttachmentJsonData.value.logReference",{{"id", "TestRunId"}, {"url", "TestRunURL"}, {"ApiUrl", "TestResultsApiUrl"}, {"name", "TestRunName"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"incompleteTests", "notApplicableTests", "passedTests", "unanalyzedTests", "revision"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"JsonData.value.project.name", "ProjectName"}, {"JsonData.value.startedDate", "StartDate"}, {"JsonData.value.completedDate", "CompletedDate"}, {"JsonData.value.testCase.id", "TestCaseId"}, {"JsonData.value.testCase.name", "TestCaseName"}, {"JsonData.value.testPoint.id", "TestPointId"}, {"JsonData.value.lastUpdatedDate", "LastUpdatedDate"}, {"JsonData.value.createdDate", "CreatedDate"}, {"JsonData.value.testPlan.id", "TestPlanId"}, {"JsonData.value.owner.displayName", "Owner"}, {"JsonData.value.runBy.displayName", "runBy"}, {"AttachmentJsonData.value.logReference.filePath", "AttachmentsName"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns2",{{"StartDate", type datetime}, {"CompletedDate", type datetime}, {"CreatedDate", type datetime}, {"LastUpdatedDate", type datetime}})
in
#"Changed Type"

[v-lgarikapat · Accepted solution]

Hi @sravs2007 ,
Thanks for your response.

The Power Query script is mostly well-structured, but there are a few areas that could be causing issues:

Inconsistent Base URLs: You're mixing https://vstmr.dev.azure.com/ and https://dev.azure.com/. It's best to stick with https://dev.azure.com/ consistently unless you have a specific reason to use vstmr.

Incorrect Use of id in URL Construction: After renaming "JsonData.value.id" to TestResultId, the original id (likely referring to the test run ID) might not be available when you build AttachmentApiUrl. Make sure to preserve the test run ID earlier and use it explicitly, like TestRunId.

Authentication Token Format: Ensure your PAT has the correct scope and that the encoding is correct:

encodedPat = "Basic " & Binary.ToText(Text.ToBinary(":" & pat), BinaryEncoding.Base64)
You can verify the API with Postman or curl first to confirm access.

Overuse of try ... otherwise null: This silently swallows errors, making debugging difficult. Temporarily remove try blocks so Power Query shows the actual error messages.

Too Many API Calls: You're calling Web.Contents for each test run and result. This can hit rate limits or significantly slow performance. Consider filtering or paging the initial API call to reduce the data volume.

Expansion of Nested JSON: Some fields like logReference may not be present in every row. Use checks like Record.HasFields before expanding to prevent errors.

Column Rename Timing: Renaming key columns (like id) too early can cause later steps to fail. Delay renaming until you're done using the original field names in URL construction.

Get started with the REST APIs for Azure DevOps Services and Azure DevOps Server - Azure DevOps Serv...

If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana

[v-lgarikapat]

Hi @sravs2007 ,

If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who may encounter the same issue to find answers more efficiently.

If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana.

[v-lgarikapat]

Hi @sravs2007 ,

If your question has been answered, kindly mark the appropriate response as the Accepted Solution. This small step goes a long way in helping others with similar issues.

We appreciate your collaboration and support!

Best regards,
LakshmiNarayana

[v-lgarikapat]

Hi @sravs2007 ,

As we haven't heard back from you, we are closing this thread. If you are still experiencing the same issue, we kindly request you to create a new thread we’ll be happy to assist you further.

Thank you for your patience and support.

If our response was helpful, please mark it as Accepted as Solution and consider giving a Kudos. Feel free to reach out if you need any further assistance.

Best Regards,

Lakshmi Narayana

[v-lgarikapat]

Hi @sravs2007 ,

Thanks for reaching out to the Microsoft fabric community forum.
I'd like to suggest that you refer to the following thread about dynamic data sources

Solved: Dynamic data sources aren't refreshed in the Power... - Microsoft Fabric Community
Troubleshooting the Power Query Web connector - Power Query | Microsoft Learn

If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.