Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

smallint values from sql query shown as data type text

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

 

  • 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

     

5 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion

    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's avatar
      Anonymous
      Not applicable

      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's avatar
        Mariusz
        Community Champion

        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

         

  • 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.

  • HenrikJeppsson's avatar
    HenrikJeppsson
    Frequent Visitor

    I can report that this still not has been resolved tree year later. Anyone having an ideomatic way to workaround this?