I use a dataflow gen2 to import a table from SQL Server and there is a smallint column (let's call it StatusID) that shows up automatically in the dataflow UI as 'Whole Number', which makes sense. I create a new lakehouse table with the dataflow and now I have a StatusID column that shows up as '12S StatusID' in the lakehouse explorer.
There are only a few distinct values in the StatusID column, -1, 0 and 1. As I populate the table in the lakehouse, I noticed a discrepancy when I verify data in the SQL endpoint:
select count(StatusID), StatusID from dbo.table1 where StatusID = 1 group by StatusID
select count(*) from dbo.table1 where StatusID = 1
Both queries are supposed to return the same number but they don't. Some records are not included in the count of the second query. Additionally, when using this column in a Power BI report, the distinct values are -1, 0, 1 and 65535.
This is the workaround I used: in the dataflow UI, change the StatusID data type from 'Whole Number' to...'Whole Number'. What I want to do is to set StatusID explicitly to Int64.Type in Power Query and use the dataflow to create another new lakehouse table. This time, StatusID shows up as '12L StatusID' in the lakehouse explorer and both queries show the same result.
What is going on here? Something is not right. Smallint is supported in Fabric and '12S' shouldn't cause counting discrepancies.
