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

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.

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

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..., TitleLevel2, TitleLevel2, TitleLevel3, TitleLevel4, TitleLevel5, TitleLevel6, TitleLevel7, TitleLevel8), Tester($select=UserName)", null, [Implementation = "2.0"] )

       

      …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, [Implementation="2.0"] ) in Source

                  

                 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.

                     

                 4.  

                 5.  

               •  

             •  

           •  

           •  

         •  

    •  

    •  

  •  

  •  

  •