Help expanding tester

[stu1998]

Hello all,  I am trying to retrieve the latest outcome for a given test plan and the tester who ran the test.  I am able to retrieve the results, however the Tester value is 'Record' (the field needs to be expanded).  When I expand the table, i get an 'error' message for each result (instead of the Tester's User Name.  Can someone point me to how to resolve this?  

Query I am using

let
Source = OData.Feed("https://analytics.dev.azure.com/mycompany/myproject/_odata/v3.0-preview/TestPoints?" & "$apply=filter((TestSuite/TestPlanTitle eq 'MyTopLevelTestPlan' and TestSuite/IdLevel2 ne null and TestSuite/IdLevel3 ne null))"),
#"Filtered Rows" = Table.SelectRows(Source, each ([LastResultOutcome] = "Passed")),
#"Expanded Tester" = Table.ExpandRecordColumn(#"Filtered Rows", "Tester", {"UserName"}, {"Tester.UserName"})
in
#"Expanded Tester"

Error in the Tester.Username field

DataSource.Error: OData: Request failed: The remote server returned an error: (400) Bad Request. (VS403483: The query specified in the URI is not valid: VS403489: The Analytics Service doesn't support key or property navigation like WorkItems(Id) or WorkItem(Id)/AssignedTo. If you getting that error in PowerBI, please, rewrite your query to avoid incorrect folding that causes N+1 problem..)
Details:
DataSourceKind=OData
DataSourcePath=https://analytics.dev.azure.com/mycompany/myproject/_odata/v3.0-preview/TestPoints(152004634)/Tester
Url=https://analytics.dev.azure.com/mycompany/myproject/_odata/v3.0-preview/TestPoints(152004634)/Tester

[Adamboer]

It looks like the error is caused by the attempt to expand the Tester record in the OData feed. According to the error message, the Analytics Service doesn't support key or property navigation like "WorkItems(Id)" or "WorkItem(Id)/AssignedTo".

One way to resolve this issue is to try to avoid expanding the Tester record in the query. Instead, you can retrieve the Tester ID and use it to join with another table that contains the Tester's User Name. Here's an example query that shows how to do this:

let
Source = OData.Feed("https://analytics.dev.azure.com/mycompany/myproject/_odata/v3.0-preview/TestPoints?" & "$apply=filter((TestSuite/TestPlanTitle eq 'MyTopLevelTestPlan' and TestSuite/IdLevel2 ne null and TestSuite/IdLevel3 ne null))"),
#"Filtered Rows" = Table.SelectRows(Source, each ([LastResultOutcome] = "Passed")),
#"Extracted Tester ID" = Table.TransformColumns(#"Filtered Rows", {"Tester", each Record.Field(_, "Id")}),
#"Merged with Tester Table" = Table.NestedJoin(#"Extracted Tester ID", {"Tester"}, TesterTable, {"Id"}, "Tester", JoinKind.LeftOuter),
#"Expanded Tester UserName" = Table.ExpandTableColumn(#"Merged with Tester Table", "Tester", {"UserName"}, {"Tester.UserName"})
in
#"Expanded Tester UserName"

In this example, I'm assuming that you have another table called TesterTable that contains the Tester ID and User Name. You can join the TestPoints table with the TesterTable using the Tester ID, and then expand the User Name field from the joined table.

[stu1998]

Thank you.  This sounds like it would work, but I don't see any way to retrieve the Tester ID from DevOps w/o the same query and expanding the Tester column

[Anonymous]

Hi @stu1998 ,

The error message indicates that the query specified in the URI is not valid^[5]. The error message also suggests that the Analytics Service doesn't support key or property navigation like WorkItems(Id) or WorkItem(Id)/AssignedTo. If you are getting that error in PowerBI, please rewrite your query to avoid incorrect folding that causes N+1 problem.

You can try to rewrite your query to avoid incorrect folding that causes N+1 problem. You can also try to use the OData.Feed function to retrieve the Tester's User Name.

You can go through this article to know available OData query string options.

On Power BI side, to get the data use Odata feed data source, for example Dynamics CRM, you can take a look at this article: Prefilter data for Power BI with OData URLs. The entered URL should follow the syntax of above OData URI.

Maybe you are also interested in Filter a report with a URL query string parameter.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.