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!
Are you using a gateway by any chance? what's your data source? are you able to reproduce this error in a different workspace?
Hi Miguel,
No gateway
Data source is a json file in the lake in the workspace
Yes, have created an entirely new workspace with a new pipeline to call the API and save the output as a json file and dataflow to flatten out the json, and get the same result
Could you share the table schema of the table that you have as the output in your dataflow gen2? what data types does it have?
The article below might help you understand some of the limitations that we have in Dataflows Gen2 today. Please check if you might be hitting one of them:Data Factory Dataflow Gen2 limitations - Microsoft Fabric | Microsoft Learn
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.