Error when i tried to refresh ADOTesPoints OData in Dataflow, works fine in PowerBI Desktop M Query,

[sravs2007]

Am trying to pull data from ADO Testpoints OData which is created in Dataflow. same query works fine in PowerBI Desktop

let

  Source = OData.Feed("https://analytics.dev.azure.com/MyOrg/MyProj/_odata/v3.0-preview/TestPoints?$select=LastResultOutcom..., TitleLevel2, TitleLevel2, TitleLevel3, TitleLevel4, TitleLevel5, TitleLevel6, TitleLevel7, TitleLevel8), Tester($select=UserName)", null, [Implementation = "2.0"])

in

  Source

Error

Error: Data Source Error : DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (Internal Server Error): MashupValueException. RootActivityId = dc0f8136-0924-467d-a31b-88b38a41216b.Param1 = DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (Internal Server Error): MashupValueException Request ID: dbec3c04-90a8-4039-59d8-a7e3a71ee537.

[Aaliyan123]

The key clue here is that your query works in Power BI Desktop but fails in a Dataflow, returning a 500 Internal Server Error from the OData endpoint. This is actually a common scenario with Azure DevOps (ADO) OData feeds because Power BI Dataflows run in the Power Query Online (cloud) environment, which has stricter limitations than Power BI Desktop. Let me break this down and guide you on what’s likely happening.

---

1. Difference between Desktop and Dataflow

• Power BI Desktop:

  • Runs queries locally.

  • Can handle larger payloads.

  • Supports $expand, complex $select, and nested queries more liberally.

  • Power BI Dataflow (Power Query Online):

    • Runs in the cloud (Power Query Online engine).

    • Limited memory and timeout restrictions.

    • Some OData query constructs, especially complex $expand or multiple nested $select fields, often fail in cloud, even if they work locally.

      So a query like yours:

       

      OData.Feed( "https://analytics.dev.azure.com/MyOrg/MyProj/_odata/v3.0-preview/TestPoints?$select=LastResultOutcom...

      …works in Desktop but may be too complex for Dataflow. Also, notice the TitleLevel2 appears twice in your $select, which may cause cloud parsing issues.

      ---

      2. Common Causes of 500 Internal Server Error in Dataflows

      1. Too many fields in $select or $expand

         • Power Query Online has stricter limits on query length.

         • Nested $expand queries

           • Example: Tester($select=UserName)

           • Can sometimes break in Dataflows even though Desktop handles it.

           • Preview OData endpoints (v3.0-preview)

             • Some preview endpoints return 500 in cloud due to unsupported operations.

             • Duplicate fields or syntax issues

               • $select=..., TitleLevel2, TitleLevel2,...

               • Desktop may ignore duplicates; cloud often fails.

                 ---

                 3. Recommended Fixes

                 A. Simplify the query

                 Start with a minimal query to confirm connectivity:

                  

                 let Source = OData.Feed( "https://analytics.dev.azure.com/MyOrg/MyProj/_odata/v3.0-preview/TestPoints?$select=Id,Title", null,...

                 If this works, gradually add fields back one by one. This will reveal the field(s) causing the cloud engine to choke.

                 ---

                 B. Avoid nested $select in $expand

                 Instead of:

                  

                 Tester($select=UserName)

                 Try pulling the related table separately and merging in Power Query Online. For example:

                 1. Load TestPoints without Tester.

                 2. Load Testers table separately (/Testers endpoint).

                 3. Merge in Power Query Online using UserId.

                    ---

                    C. Remove duplicate $select fields

                    You have TitleLevel2 twice. Make it unique:

                     

                    $select=LastResultOutcome, TitleLevel2, TitleLevel3, TitleLevel4, TitleLevel5, TitleLevel6, TitleLevel7, TitleLevel8

                    ---

                    D. Use OData “Version 4”

                    ADO OData v3.0-preview works fine in Desktop, but sometimes Dataflows handle Implementation="2.0" (OData v4) better. Try changing:

                     

                    [Implementation="2.0"]

                    to

                     

                    [Implementation="2.0", Headers=[#"Accept"="application/json;odata.metadata=minimal"]]

                    ---

                    E. Test with fewer records

                    Sometimes cloud fails due to large payload. Add $top=10:

                     

                    ?$top=10&$select=Id,Title

                    If this works, the problem is likely a timeout or cloud memory limit.
                    
                    

                    If you want, I can rewrite your exact query so it works in Power BI Dataflow while keeping all the fields you need, avoiding nested $select and duplicate fields. This usually fixes the 500 error.

[sravs2007]

Thank you, @Aaliyan123, for the detailed explanation on Desktop and Dataflows. I’ll work on adjusting the query based on your suggestions and revalidate.
I do have one clarification: my dashboard is taking a long time to refresh because it’s pulling a large number of WorkItems and WorkItemLinks across multiple projects. What would be the best approach for bringing data from ADO into Power BI in this case? Since Dataflows require constructing queries with certain limits, Analytics Views can’t be used for WorkItem links,  I can pull the required data in Desktop with OData, but the refresh performance is slow.

[sravs2007]

Dataflow while keeping all the fields you need, avoiding nested $select  fields. This usually fixes the 500 error.

Rather than pulling all columns, am filterning minimal columns at source, is it no correct way

[v-aatheeque]

Hi @sravs2007 

Filtering only the required columns at the source is the right approach and it is a Power BI best practice.

The suggestion to avoid nested $select fields is not asking you to load all columns, but to simplify the query structure that Power BI generates. In some cases, Power BI folds multiple steps into a complex OData query and certain OData services fail with a 500 error when handling nested $select, $expand, or combined query options.

[sravs2007]

ower BI folds multiple steps into a complex OData query and certain OData services fail with a 500 error when handling nested $select, $expand, or combined query options. how to avoid this..

[v-aatheeque]

Hi @sravs2007 

Power BI may fold multiple Power Query steps into a single, complex OData request. Some OData services don’t fully support advanced or nested query options (such as combined $select, $expand, $filter, etc.), which can result in a 500 Internal Server Error.

Best Practices to Avoid OData 500 Errors in Power BI:

• Keep the initial OData request as simple as possible.
• Avoid complex or nested $expand operations in a single query.
• Split complex logic into multiple queries and perform joins/transformations within Power BI instead of pushing everything to the OData source.
• Test with a basic query to confirm the endpoint works, then gradually add transformations.

Hope this helps!!

Thank You.

[v-aatheeque]

Hi @sravs2007 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[sravs2007]

How about DB storage, load data into DB and then Connect DB .. will this approach work

[v-aatheeque · Accepted solution]

Hi @sravs2007 

Yes, staging OData data into a database and connecting Power BI to the DB is a recommended best practice. It avoids OData service limitation and typically results in more stable refresh and better performance.

Reference : https://learn.microsoft.com/en-us/power-query/connectors/odata-feed

Hope this helps!!
Thank You.

[v-aatheeque]

Hi @sravs2007 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[v-aatheeque]

Hi @sravs2007 

Thanks for raising this query in Microsoft Fabric Community Forum.
A 500 (Internal Server Error) from an OData source means the service failed to process the request. This is usually caused by a temporary service issue, an invalid/unsupported query, or limitations on the source side when Power BI pushes down the query.

• Confirm that the OData connector is supported for Dataflows in your workspace. Some connectors work in Power BI Desktop but have limitations in the Service.
• If the OData source uses Microsoft Entra ID (OAuth), the refresh token may expire during Service refresh. Re‑authenticate the dataflow and verify credentials in the Service.
• Double‑check your query logic for discrepancies compared to what works in Desktop. Ensure all columns being pulled are correctly defined in the query.
• Ensure the on‑premises data gateway is online, correctly mapped, and updated to the latest supported version. Verify the dataflow is correctly bound to the gateway.
• Large payloads or complex OData queries can fail in the Service due to timeouts or resource limits. Try reducing the data volume (filters/date range) to test if this is a size or timeout issue.
• Paste the OData URL into a browser to confirm the service itself responds correctly. If it fails there, the issue is server‑side.

Reference : Troubleshoot refresh scenarios - Power BI | Microsoft Learn

Hope this helps !!

Thank You.

[sravs2007]

 when i tried to pull data of Test suites didn't find this issue. this error is coming only for WorkItems and testpooints

[Mauro89]

Hi @sravs2007, 

as of the documentation (Access Analytics data from Power BI Desktop - Azure DevOps | Microsoft Learn) this should work.

Some things you can check:

- do you have proper rights in the ADO org/project?

- check the endpoint schema from the docs. The URL in your code snipped seems to be of what the docs say

- authenticate with your organizational account

- go to a lower version of the endpoint (e.g. V1.0) and check again (this version at least works on my end)

Hope this helps!

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

[sravs2007]

am having an acess to this project and anlytics is enabled ... when i hit the url in browser i can see response

desk top am getting data, but in dataflow throwing an error