Forum Discussion
Helper IData Variances between dataflow output and Lakehouse data
Hi Everyone. Been troubleshooting this for a while now.
I have a query in a dataflow that writes into Lakehouse. The result of the query (Power query preview) from the dataflow is as expected. However when I run the dataflow and the table is created in Lakehouse, I see a different result in my amount column. Has anyone faced this before? The data value in Lakehouse is lower than that in the dataflow.
Hi sufregs,
What you see in the Power Query preview isn’t always what gets materialized in the Lakehouse. During the actual dataflow run, the Lakehouse table schema is created (or inferred), and at that point numeric columns can be converted into a different type or precision than what you see in preview. This often results in amount values being lower due to rounding or truncation, especially if the column ends up with insufficient decimal precision or is treated as an integer or floating‑point value.
In my experience, the fix is to be very explicit about data types right before the data is written: ensure the amount column is set to the correct decimal or fixed decimal type, and be mindful of locale when converting from text to numbers (decimal separators can bite here). It also helps to check the actual Lakehouse table schema after the load and compare it to the Power Query types, because any mismatch there is usually where the discrepancy is introduced.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others. If you resolved your question, let us know what worked for you.
4 Replies
- nielsvdc
Super User
Hi sufregs,
What you see in the Power Query preview isn’t always what gets materialized in the Lakehouse. During the actual dataflow run, the Lakehouse table schema is created (or inferred), and at that point numeric columns can be converted into a different type or precision than what you see in preview. This often results in amount values being lower due to rounding or truncation, especially if the column ends up with insufficient decimal precision or is treated as an integer or floating‑point value.
In my experience, the fix is to be very explicit about data types right before the data is written: ensure the amount column is set to the correct decimal or fixed decimal type, and be mindful of locale when converting from text to numbers (decimal separators can bite here). It also helps to check the actual Lakehouse table schema after the load and compare it to the Power Query types, because any mismatch there is usually where the discrepancy is introduced.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others. If you resolved your question, let us know what worked for you.
sufregsI have done this using Decimal.Type but the variance still exists. Checked the lakehouse table schema and it does show as data type decimal (not float) which is consistent with Decimal.Type in the Power query. I have also observed that this happens just after my Table.Combine step. I have set same column in all tables to same data type prior to combine but the issue persists.
It also seems I submitted this issue twice in the community. below is the link to the other (please respond using that), not quite sure how to merge both.
Data Variances between dataflow output and Lakehou... - Microsoft Fabric Community- nielsvdc
Hi sufregs,
After the Table.Combine step, could you check whether the dataset in Power Query contains more or fewer rows than the table in your Lakehouse? The easiest way is to compare the row counts in both places, or validate by checking the sum of your amount column.
That should help you identify what’s happening before or during the combine step. One or more source tables used in the Table.Combine step might already be filtering rows, or the combine itself could be introducing duplicates.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others. If you resolved your question, let us know what worked for you.
In this forum you cannot merge posts. And because someone already your question, you probably cannot delete the post.
- nielsvdc
Hi sufregs,
After the Table.Combine step, could you check whether the dataset in Power Query contains more or fewer rows than the table in your Lakehouse? The easiest way is to compare the row counts in both places, or validate by checking the sum of your amount column.
That should help you identify what’s happening before or during the combine step. One or more source tables used in the Table.Combine step might already be filtering rows, or the combine itself could be introducing duplicates.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others. If you resolved your question, let us know what worked for you.