smallint values from sql query shown as data type text

[Anonymous]

Dear all,

I have a power query for a table from MS SQL Server. This table contains a column of type smallint, but the value is shown in Query as type "decimal number".

--> Why does PowerBI not detect the most fitting data type?

Here my query which leads in PowerBI June2020 update to above behavior:

let
   Source = Sql.Database("MyServer", "Reporting"),
   Query = Value.NativeQuery(Source, "
SELECT
   Equipment -- PROBLEM: is smallint, which would be shown as decimal number in PBI
   ,CAST(Equipment AS int) 'Equipment' -- WORKAROUND: by conversion to int it is shown correctly as whole number in PBI
FROM [Reporting].[dbo].[Lines]
WHERE Equipment = 13")
in
   Query

[AnonymousPerson]

Yeah Power Query has no ieaa what a SMALLINT or TINYINT is. So you have to go out of your way to convert them in a foldable way. Same goes for MONEY and SMALLMONEY. Power Query should think they're FIXED DECIMAL. But Power Query don't care. Power Query does whatever it wants.

I just convert/cast them as INT in my source SQL views. It's nice to have that SQL view layer between DW tables and Power BI to do little rinky dink stuff that accounts for Power Query's shortfalls, like this.

[Mariusz · Accepted solution]

Hi @Anonymous 

Normally it does it automatically, but if it hasn't you can add this step by going to Transform > Detect Data Type or click on the data type on the left of the column and pick the type manually.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi Mariusz,

yes the solution of converting the values inside PowerBI works and should not lead to much performance impact as it is a numeric conversion.

Also the conversion detection button works by suggesting "whole number" as target data type.

I do the conversion a step before in SQL Server by "CAST(myVal as INT) myVal".

I'm wondering, why "int" values of sql queries are detected as whole numbers, but "smallint" values as "decimal numbers". Looks like a bug to me?

Best regards,

Thomas

[Mariusz]

Hi @Anonymous 

Just tested on my end and it's doing the same thing with small and tiny int as well, surely this looks like a bug.

If you wish to report it, you can do it here:
https://community.powerbi.com/t5/Issues/idb-p/Issues

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn