Forum Discussion
Gen2 Dataflow Failed to insert a table
So I think I've figured it out. It seems to happen when you have a referenced query that still has records in it that show as [list]. Even when this query isnt set to output to a destination.
Disabling staging on the main, referenced query, fixes it. Hopefully in the future the staging storage will support more record types, but at the very least the error could be cleaned up as it isnt helpful at all.
Explanation of what I was trying to do:
The JSON has a list of Power BI workspaces and all of their content, so I have the main query that points to the JSON and unpicks the Odata response. It returns the Workspace ID and then the lists of data for Reports, Users, Datasets etc.
I then reference the main query to create a table of reports. I keep the Workspace ID but delete everything else other than the Reports column, which I then expand. Repeat that for Users and Datasets. Workspace ID appears in every one of these alongside the expanded info.
I only set a destination for the flattened queries, not anything that still contains lists/records
If I flatten the entire thing in a single query, then it will load happily. But that's a stupid setup as it means massively duplicating everything.
Hi,
Did you pass the JSON by a JSON validator online?
I know it sounds obvious, but I found some references of the same error message with problems related to the source : https://community.fabric.microsoft.com/t5/Power-Query/Microsoft-Mashup-error-message-on-refresh/td-p/1981368
As a debugging strategy, maybe it would worth if you could make the JSON a static JSON at some place and run the pipeline over it and, if it works, add the connection to the API ?
By this way you could break down the root cause, identifying if the problem is the JSON you can see returned or the API access returning something different and weird.
Kind Regards,
Dennes
Yeah it's valid json and Power Query parses it fine. The data pipeline API call writes it to the lake as a .json file, so it's already static. It just can't save the output (a single table for the main data object) into the lake or warehouse.
Hi,
If there is no better solution, I would suggest breaking down the JSON until find what is causing the problem.
What I mean: I imagine the JSON has many records. Even if the data is hierarchical, it still may have many records. What if you create a fake JSON with one single record from the original JSON you have ? Does the pipeline work?
If it works, it would prove the problem is in one of the records. You could break down the JSON (half records? Add in small amounts?) until you locate exactly which record is causing the problem.
Anyway, it's just a debugging suggestion, in case no better option is available.
Kind Regards,Dennes