Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
david_piepgrass
Frequent 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
Frequent 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
Frequent 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.