Forum Discussion
Key is too long for Power Query creating duplicate keys
Hi All,
So I am importing a table from snowflake with business keys with a length of 18 characters. Upon loading in PowerBI via Power query I am bound by the length limitation of the integer of 15 characters. As such my keys are rounded on the last 3 digits (see table) and giving me errors with duplicate keys in my datamodel.
I know I can convert to whole number in power query using TransformColumnType, but this seems to affect my query folding/ incremental loading. From what I understood I should be able to do this upon my import using a cast or format, but so far no luck.
| 237608206420221003 | 237608206420221000 |
| 237608206420221024 | 23760820642022100 |
| 237608206420221010 | 237608206420221000 |
| 237608206420221107 | 237608206420221000 |
I tried the below but did not work
- select CAST(TABLEKEY AS BIGINT) from EDM.TABLE
Any ideas?
18-19 digits is the hard limit in power query M according to: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#fixed-decimal-number
But in your case, looks like you might be able to break it into 3 smaller parts? e.g. 2376082064 20221107 237608206420221000
4 Replies
18-19 digits is the hard limit in power query M according to: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#fixed-decimal-number
But in your case, looks like you might be able to break it into 3 smaller parts? e.g. 2376082064 20221107 237608206420221000
Sander8000 It also looks like there are repeating values within the keys. Those could be good options for separation/elimination also. If these are intended to be used as unique keys it would be good to understand the components used to create this long key and the purpose; what constitutes each set of characters in the key and what does that set represent. If none of this is known then you could create an index for these which would immediately drop the length, however, if you are needing to join with other tables, you would need to know the logic so you could do it correctly.
I was able to cut them in half: 237608206420221107 reducing the size to 18 character, but this is still too much for power query (integer format). The length of this key would limit some features in Power BI desktop indeed, but the key is not needed for visuals, purely for data modelling.
To import the data in powerbi desktop I use a SQL statement to import my data. I was hoping I could transform the dat upon import thereby hopefully not impacting my query folding and incremental loading.
Something like:
- select CAST(TABLEKEY AS BIGINT) from EDM.TABLE
thanks all for your help. This worked for me,
Cheers
