Forum Discussion
DataflowGen2, which does not copy all the data...
I'd still recommend raising the support ticket so an engineer can take a closer look at your scenario and get the traces for it.
Would you happen to have a way in which we can reproduce the issue?
I initially tried to create a Dataflow with the sources you mentioned but was never able to get the same result that you describe. It could be something quite specific about your query and/or your sources, so a deeper investigation through the support route would be the best place to get a much closer look.
Before creating a ticket, I wanted to see if the community had already encountered this problem, as it does not seem specific to my environment.
Yes, to reproduce the problem, I think you just need to:
- Have a source table via the Dataverse connector with many columns (the account table on my system has more than 300 columns)
- Copy the data to a table with the same structure in a lakehouse
With the first code, some rows are lost, while with the second code, all rows are transferred.
In fact, as shown in this screenshot, with the first code, not all rows are transferred because it cannot read all the rows.
It transfers all the lines that are read, but since it cannot read everything, not all of it is transferred.
could you confirm whats the code being passed for each of those queries? the screenshot uses references not visible (specifically Dataverse_Tablename)
I specified this in my reply here (dbo.Account).
Thanks for the info!
I'd recommend removing Staging from your queries if its enabled. Would also recommend removing the add custom column step to see if you get the same issue and to isolate the variables as you've mentioned that the issue could be strictly with the Value.NativeQuery usage.
There's a situation where the native query might not give you the same data that you get through the navigator of a connector. However, the difference would be at the table schema shown in the query and not the number of rows. This is because the connector provides projections or reference columns that have either "record" or "table" values which are not supported to be loaded to a Lakehouse, but they'll show up as columns in your query. A native query wouldn't "project" or provide those column references.
I finished doing some tests and I can see a behavior that I'd like to explain.
When we create a connection to a source, we may need to read multiple rows or make multiple calls to get the data that we need. In the activity statistics it may show up the total of all the rows that we read to perform the activity, but it doesn't directly mean that it was the output of the query that could've ran inside of the activity.
Both of the attemps that I had show this behavior:
But they write the same number of rows which is the number of rows that your query, in theory, should have.
You can run 2 validations to make sure that your query is evaluating to the number of rows expected:
- You can head over to the Power Query editor and in the "Transform" tab of the ribbon add a "Count rows" transform to read the total rows. After doing that it should match the "rows written" (in theory) - in my case it does match for 18 rows:
- You can check in your PowerApps admin access how many rows that table has. In my case it also matches the 18 rows written.
If both the queries that you've created are exactly equivalent from each other and they yield the same number of rows within the Power Query Editor (you can validate this by the process stated in #1 using the "Count rows"), then this is something that we would like to understand more and should be raised as a support ticket so an engineer can take a closer look. With a support ticket we would get to the bottom of your scenario and determine why one query loads X number of rows whereas the other might load Y.
- You can head over to the Power Query editor and in the "Transform" tab of the ribbon add a "Count rows" transform to read the total rows. After doing that it should match the "rows written" (in theory) - in my case it does match for 18 rows: