There is an option to load data to multiple table in one go in Dataflows. These tables can be related. Dataflow engine calculates the dependencies and determines the correct order for loading. It’s a very cool functionality, however, you have to be aware of some potential issues as well as available workarounds.
Problem: I am trying to load bills and meter data in to Dataverse tables. The Meter table’s linked to the Bill table. In my datasets, I load data from two CSV files. I’ve got a common Account ID column which is a unique key for the load. However, the actual alternate keys are Name (Bill) and Name (Meter).
Load Attempt 1:
Let’s load data from the Bills CSV file.
Let’s add the unique Name column.
Now let’s add another query, for Meter Data CSV file.
To link two datasets, let’s merge tables on Account ID.
Now we’ve got the link between Bills and Meters.
Let’s add the uniques Meter Name column.
Let’s continue to the mappings for Bills…
And for Meters.
Always make sure you mapped Names. or whatever your alternate key is.
We also map the reference from Bill to Meter.
Not so fast! The dataflow is failing with the tragically “informative error:
There was a problem refreshing the dataflow, the dataflow definition contained errors. Please fix the problem and try again. Additional information: Unexpected exception, Message: Expression.Error: The column 'XXXX ID' of the table wasn't found.
Whatever. Mama, I think I’ve just broke the engine…
I don’t know but (after hours of thinking I remembered I fixed it before) I tried loading both tables separately and it worked. So it’s something about the reference column. I believe it got lost in Merge trying to figure out dependencies.
Let’s save it and ourselves from the headache. Let’s split the calculation of the reference column and load to keep loading tables “independent”. This is the idea.
Let’s duplicate the parent, Bills, query.
The Merge to get the reference column, we perform on the duplicated set Bills(2). We aren’t going to load it though.
We’ve got our reference to Bills(2). But datasets Bills and Meters aren’t linked.
This time dataflow didn’t fail and data has been successfully loaded to Dataverse.
With the link to Bills.
8 thoughts on “❤ Dataflows for Power Apps ❤: “The column ‘[Column_name]’ of the table wasn’t found.” error while loading multiple tables”
Thank you so much Olena! Your solution is single and unique. And your solution helped me.
LikeLiked by 1 person
I’m glad I could help 🙂
Thank you so much, Olena! Your solution is single and unique. And this really helped me.
A little bit more about my case. Import from Azure Blob Storage to Dataverse with merging a file list from Blob Storage with Dataverse`s table (e.g. import files log). In this point, in merging, I`ve got issues that you described above.
I hope we get better troubleshooting tools one day.
How the dataflow identifies the order here that the Meter table should be loaded after the Bills table is loaded? Because Meter is related to Bills (2) and Bills(2) is not even loaded in dataverse.
Microsoft controls it. They use a black magic to figure the order out 🙂