cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
david_piepgrass
Regular Visitor

Nested tables: "Column 'Id' in Table...contains a duplicate value '241' and this is not allowed"

Our OData API returns objects that contain nested child rows, something like this:

 

{
"@odata.context": "https://localhost:5001/odata/$metadata#entities",
"value": [
{
"Id": 1,
"Name": "The Project",
"Type": "Project",
"Days": [
{
"Date": "2021-01-08T00:00:00-07:00",
"Deposit": 0.0,
"FlowRate": 0.0
},
{
"Date": "2021-01-09T00:00:00-07:00",
"Deposit": 0.0,
"FlowRate": 0.0
},
{
"Date": "2021-01-10T00:00:00-07:00",
"Deposit": 0.0,
"FlowRate": 0.0
}
]
},
{
"Id": 3,
"Name": "The River",
"Type": "River",
"Days": [
{
"Date": "2020-12-29T00:00:00-07:00",
"Deposit": 0.0,
"FlowRate": 111.0
},
{
"Date": "2020-12-30T00:00:00-07:00",
"Deposit": 0.0,
"FlowRate": 1111.0
},
{
"Date": "2021-01-01T00:00:00-07:00",
"Deposit": 0.0,
"FlowRate": 1900.8
},
...
]
},
...
]
}

Clearly, PowerBI understands this data, and allows me to expand it in the Query Editor:

david_piepgrass_0-1638427133856.png

However, when I click Close & Apply, I get an error!

david_piepgrass_1-1638427199416.png

What can I do about this?

1 ACCEPTED SOLUTION
david_piepgrass
Regular Visitor

The problem turned out to be that Power BI assumes relationships that don't exist. Specifically, it assumes that if two objects both have an Id, there is a 1-to-1 relationship between them:

david_piepgrass_1-1638484333826.png

The solution is to delete these relationships before expanding the child rows.

Now, for some reason after expanding the tables, it re-reads the same information from the OData API. But at least the error is gone!

View solution in original post

2 REPLIES 2
david_piepgrass
Regular Visitor

The problem turned out to be that Power BI assumes relationships that don't exist. Specifically, it assumes that if two objects both have an Id, there is a 1-to-1 relationship between them:

david_piepgrass_1-1638484333826.png

The solution is to delete these relationships before expanding the child rows.

Now, for some reason after expanding the tables, it re-reads the same information from the OData API. But at least the error is gone!

In addition, the same error happens if the correct relationships exist! But I am told that there is a workaround: expand the tables first, then create the relationships afterward.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors