Forum Discussion

GeorgeVepkhvadz's avatar
GeorgeVepkhvadz
Icon for Helper I rankHelper I
1 year ago

Binary(36) Data type from Snowflake to PBI

In the snowflake we have warehouse where all keys have binary(36) data type, which is not readable in PBI, what would be the best and most efficient way to load those keys in PBI?  I tried casting, h...
Need Help
danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi GeorgeVepkhvadz 

You can try this:

Binary.ToText([Content], BinaryEncoding.Hex)

I have tables over 100 mil rows, so efficiency is a big priority. But since this is a very large dataset, you can't expect this query to be efficient. This ideally should be done at the source.

GeorgeVepkhvadz's avatar
GeorgeVepkhvadz
Icon for Helper I rankHelper I
1 year ago

Yup, having such strings as keys in a huge dataset will be very inefficient. That's why I am trying to do the changes, at source:

ABS(HASH(FK_PRODUCT_CATEGORY)) % 100 AS Product_Category_ID 

I have tried smth like this and then increasing or decreasing length of hash based on the cardinality. But for some columns I didn't use any shortening. However, when importing full hashed values, and creating relationship in PBI, most of values which matched in binary data types, don't match in hashed value.