Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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)
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.
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.
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
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...
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.
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.
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 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:
@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.
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?
Hello @JoseD ,
Please change the Regional Settings to English(US) or English(India).
Go to File>Options and Settings>Current File>Regional Settings.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |