Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JoseD
Frequent Visitor

All my number fields are loaded as decimals when in snowflake are integer

I'm loading several tables from snowflake, where all the number fields are integers, but they appear in powerBI as decimals, looking really bad on the dashboard, do I have another solution apart from changing one by one all the fields in powerquery? maybe something like change a configuration to load the numbers properly

 

Any help is appreciated, thanks!

 

year 2020 as 2020,00year 2020 as 2020,00

When the tables are loaded, powerquery detects all the number fields as decimal instead of as integerWhen the tables are loaded, powerquery detects all the number fields as decimal instead of as integer

in snowflake there are integersin snowflake there are integers52c0ef1b45ddbdcc811f16f305bb1047.png

15 REPLIES 15
SujanGhosh
New Member

Hi,
Please open a ticket to Snowflake support. 
https://community.snowflake.com/s/article/How-To-Submit-a-Support-Case-in-Snowflake-Lodge


There is an internal parameter that needs to be enabled at the user or account level, it will convert the decimal/number (38,0) to the whole number as expected by Power BI, and the rest such as Number(29,6) will be kept as it is without any changes.

 

Regards,
Sujan Ghosh( Snowflake)

PieterM
Frequent Visitor

Did any of you get this issue resolved?
I am facing the same problem in a different scenario: my foreign keys in the Snwoflake datamart are 18 digit hash keys. But when I import this in Power Query I am losing precision as the max digits in the numeric datatype in Power Query is 15.  The workoround to cast the keys to varchar is not preferred as the keys are of course used in the relations to the dimension tables. Any thoughts?

No, but your scenario is quite different.  

MFleissner
New Member

Hi there, 

 

exact same problem here - has there been a solution to this? 

Working in import mode in PowerBI. In Power Query the numbers (content) are shown correct as whole numbers, but the datataype still reflects decimal, even though we have explicitly casted the numbers to integer on the snowflake.

MFleissner_1-1673339152971.png


Pulling the columns into visuals/tables, the numbers are shown as decimals with two digits after the comma, e.g. Cal Year as "2018.00" which doesnt make sense at all. 

 

Tried to align the Regional settings of Snowflake, PowerBI and the VM where PowerBI is running. Tryed English (US), English (Europe), because Snowflake runs on Azure Europe (Netherlands), also tried German, which is our acutal location - made no difference at all unfortunately. 

 

Really looking forward to an update/solution to this. 

Best regards, Maria

 

CatPhish
Advocate I
Advocate I

I'm having the same problem.  It seems to be caused by the fact that Snowflake treats all integer values as number(38,0).  So, when this is imported into PBI, it is seen as a decimal type.  This is causing two problems for me...

  1. This is making my data set much larger than it needs to be.
  2. This is causing me headaches related to aggregation tables as only integers can be used for counts.

Came back here to add that you can change the format of the numbers, so that whole numbers will appear as whole numbers, but the underlying type will remain decimal.

A189
Frequent Visitor

If I try to convert the number format, it says the step is not supported in DirectQuery mode.  How did you convert the datatype without importing?

With direct query, you can't change the format of the number.  However, you can change the appearance of it.

So, while this doesn't change the amount of space the column takes in your data, it does fix appearance and formatting problems.

A189
Frequent Visitor

My specific problem isnt appearance, but the column in question is used as a join and defined in Snowflake as number(38,0). 
When the SQL query is issued to snowflake, its being cast as a char column, substringed to 1st 4000 characters and then cast as a number before being joined to another table.  This then prevents pruning as the partitioned key can no longer be used and ends up with a <1s query taking 30s.

rajulshah
Super User
Super User

@JoseD , will you be able to provide the sample data and sample file?

@rajulshah of course, I attach a link to the csv file I downloaded from snowflake with the "fact_residencias" and another link for the powerbi file, also I put some photos of how is that table store in snowflake.

powerBI file: https://jmp.sh/J9ixLSC
csv file with fact_residencias data: https://jmp.sh/pOSXVR2


photos of fact_residencias in snowflake:

bc3885a00484ba1bf23aba735cb85c9d.png

47663acb593e3ee8a6e3494407222ea0.png



 

@JoseD ,

 

Unfortunately, I cannot load the data as I don't have the credentials but when I import from the sheet that you provided, I am getting properly formatted data.

@rajulshah 

 

Yeah, when I import the data from the csv I'm also getting properly formatted data, the problem is when I'm importing the data from snowflake, what credentials is asking you?

rajulshah
Super User
Super User

Hello @JoseD ,

 

Please change the Regional Settings to English(US) or English(India).

Go to File>Options and Settings>Current File>Regional Settings.

rajulshah_0-1654751142104.png

 

Please let me know if this doesn't work.

Hello @rajulshah , thanks for you help, I tried changing Regional Settings to English(US) and English(India) but neither of them worked, I still have the issue.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.