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

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.  

               •  

             •  

           •  

           •  

         •  

    •  

    •  

  •  

  •  

  •