Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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!
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!
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |