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

[david_piepgrass]

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:

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

What can I do about this?

[david_piepgrass · Accepted solution]

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:

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!

[david_piepgrass]

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.