Forum Discussion
Resolver ICombined dataflow - most rows missing
Hello,
I am combining 9 dataflows into one dataflow. When doing so I lose most of the source rows. So for example, a source table as verified in PBI works and gives multiple millions rows of data. But the same table returns just a few tens of thousand rows in the combined dataflow. I use the gen 1 dataflow.
What could cause this? I think the total is around 40 million rows, and the combined dataflow refresh time is 1h 30 min which is less I think than the cap of 2 hours.
I have tried to look into datatypes, and the main field existing in all tables, like part number and location seem to be the same, (but I haven't verified the data type of all included fields from all source tables.) So not sure what is going on.
I have thought I could try to create a new dataflow and add one dataflow at a time and verify the result in PBI after each pass, but it is a lot of work. The filtering being made in the combined dataflow is simple, it is removing columns and filtering on part number which shouldn't have this effect on the number of rows at all.
Thanks for any help in order to track down the cause of this discrepancy. I have Googled and used AI to try to solve it but so far I haven't solved it.
// Daniel
5 Replies
1upThank you Maruthisp, I am checking and will revert with my findings.
Today I found PBI itself is changing datatype of a column, so that is a suspect now which I need to confirm from the 9 source tables what dataype they have and possibly adjust datatype at the source side.
Here's hoping for a solution.
- maruthisp
Super User
Hi 1up,
Since you confirmed that you're using Append Queries, the source column data types match, and the refresh history shows no errors, I would focus on identifying where the rows are being removed.Please try these steps:
- Open the combined Gen1 dataflow in Power Query Editor.
- Temporarily remove the Part Number filter and any other row-filtering steps.
- Check the row count of each source table using Table.RowCount().
- Check the row count immediately after the Append step, before applying filters.
- The appended row count should equal the sum of all nine source tables. If it doesn't, verify the actual row counts returned by the source dataflows and check their refresh status.
- If the count matches before filtering but drops afterwards, review the filter conditions, including nulls, leading/trailing spaces and case differences.
You can also check whether Incremental Refresh is enabled on any source or destination tables, as its configured retention period can affect which historical rows are available.
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X - Maruthi Siva Prasad - (@MaruthiSP) / X I'd start by checking the row count after each step in the combined dataflow, especially the filter on part number. Even a simple filter can remove more rows than expected if some values are null or formatted differently.
Also, are you appending the 9 tables or merging them? If you're merging, the join type could explain why so many rows are missing.
The refresh time being under 2 hours doesn't necessarily mean all the rows were loaded. Does the refresh history show any warnings?
- 1up
Thanks for the reply, JD.
Doing a row count seem like a sensible thing to try, thanks.
It is an append-query.
Refresh history show no warnings.
I have checked the data type of the part number-column which is the same in all source tables.Any additional inputs from anyone are welcome.
- ShivekMaharaj
Community Champion
Hi 1up,
Since this is an Append query, I would first prove whether the row loss happens before or after the append itself.
Microsoft's Append queries guidance confirms that append simply adds the rows from the selected tables together based on column names. It does not behave like a join, so it should not silently drop millions of rows just because the source schemas differ.
I would therefore add a temporary row count to each of the 9 source queries inside the combined dataflow before the append. If one source already shows only tens of thousands of rows there, then the append is not the cause.
At that point I would compare that source query with the original dataflow and check whether the combined dataflow is reading a linked/dependent table, applying an incremental refresh policy, or evaluating different upstream steps.
Microsoft's dataflow refresh troubleshooting guidance specifically recommends checking the refresh history CSV for Rows processed and Bytes processed when a dataflow refresh succeeds but produces much less data than expected.
That would give you a useful comparison:
- original source dataflow row count
- row count when referenced inside the combined dataflow
- row count immediately after Table.Combine
- row count after each filtering step
If the count only drops after Table.Combine, that would be unexpected and worth isolating further. If it is already low before the append, I would focus on the source/dependency refresh path rather than the append logic itself.I would also check whether incremental refresh is enabled anywhere in the chain, because Microsoft notes that dependent or linked dataflows can behave differently depending on the upstream refresh state.
So my first diagnostic would be to stop looking at the final 40 million rows and identify the first exact step where one source changes from millions to thousands.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
