PowerBI Desktop连接VSTS提取数据时遇‘Project Name字段重复’错误求助

Let's break down why this error is popping up and walk through actionable fixes to get your data flowing smoothly again.

Why This Happens #

The error Expression.Error: The field 'Project Name' already exists in the record hits specifically for Stories - [All Views] and Work Items - [All Views] because these pre-built views likely include duplicate references to the Project Name field. This could be either:

• The field was explicitly added twice in the view's configuration, or
• It's implicitly pulled in via linked/related entities (like parent work items or cross-project associations) that aren't present in the simpler Bugs and Tasks views.

Bugs and Tasks views avoid this issue because their underlying data structures don't have overlapping references to the Project Name field.

Step-by-Step Fixes #

1. Clean Up the Query in PowerBI's Query Editor #

This is the fastest fix if you don't have permissions to modify VSTS views:

• Open your PowerBI Desktop file, click Transform Data to launch the Query Editor.
• Locate the query tied to the problematic view (Stories or Work Items).
• Walk through the applied steps on the right-hand pane:
  • Look for steps like Expand or Merge Queries—these are common culprits for duplicate fields. When expanding a related table, uncheck the duplicate Project Name entry so only one remains.
  • If the duplicate is already present in the raw data, add a new step: go to Remove Columns > Remove Duplicates, select only the Project Name column, and apply the change.

2. Edit the VSTS View Configuration (If You Have Access) #

If you can modify pre-built views in Azure DevOps (formerly VSTS):

• Log into your Azure DevOps org, navigate to the project with the problematic views.
• Open Stories - [All Views] or Work Items - [All Views], click Edit View.
• Check the list of selected fields—look for two entries labeled Project Name (one might be from the work item itself, another from a linked entity like a parent epic).
• Remove one of the duplicate entries, save the view, then go back to PowerBI and refresh your data.

3. Use a Custom OData Query to Avoid Duplicates #

If pre-built views are locked or you want more control over your data:

• In PowerBI, when connecting to VSTS, select Advanced Options instead of picking a pre-built view.
• Enter a custom OData URL that explicitly selects only the fields you need, ensuring ProjectName is included once. For example:

  https://dev.azure.com/YourOrg/YourProject/_odata/v3.0-preview/WorkItems?$select=System.Id,System.Title,System.ProjectName&$filter=System.WorkItemType eq 'User Story'
  

• This bypasses the pre-built view's duplicate fields entirely.

4. Tweak the M Code Directly #

For advanced control, edit the query's underlying M code to remove duplicates:

• In the Query Editor, click Advanced Editor to view the code.
• Add a line after the data-loading step to remove duplicate Project Name columns. For example:

  // Remove duplicate Project Name columns, keep only one instance
  #"Removed Duplicate Project Name" = Table.RemoveColumns(#"Previous Step Name", List.Difference(Table.ColumnNames(#"Previous Step Name"), {"Project Name"}))
  

• Replace #"Previous Step Name" with the actual name of the step before this one in your query.

内容的提问来源于stack exchange，提问作者Brendan