Forum Discussion
Accessing Binary Columns from Oracle Database
1. just add the serial number as a column before you transform the blobs - it will automatically be expanded with the other columns.
2. as long as you keep the table narrow Power BI has no issues with billions of rows. If you want to be cute you can consider incremental refresh.
3. That was not part of the sample data - But what you can do is cut the BLOBs into chunks of 4xDATA_POINTS first and then take the first list item for each serial number, and process that.
Split your binary by positions, split into rows, interpret the result as integer.
Would be good if you could post a couple of samples.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Here is a link to sample data in pbix file, it has the data along with the expected outcome for one Binary ( I am using serial number for every row to make it more clear) : https://drive.google.com/file/d/1Jz6TfP7tUTYTeVqsTTk3eSIuF_xAg4yG/view?usp=sharing
I didn't split the rows, I only used the following line on the original binary columns since I can't copy them as they are :
Binary.ToText([DATA_ARRAY1], BinaryEncoding.Base64)If you need me to perform any kind of steps on the binary columns before using the Base64 please let me know.
It says that the encoding is invalid.
The data is cut off at 1024 bytes, maybe that is causing the error.
Anyway, since you already have the binary, do the following steps:
1. Binary.Split with a page size of 4
2. with the result do Binary.ToList
3. Now you have to check the most significant bit (bit 7 of the first value) - that is your sign, with 1 meaning negative. All the other bits from the first value and all the bits from the other three values constitute your number. You can use the Number BitShift functions to add them together.
Here is an example of the mechanics, using the brute force approach rather than two's complement.
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45WMjQwVtJRMjKFkKZQMlYnWsnUEoeEhREOCUMDEM8AjsFiFoZAtiGKmJEB2GiEWCwA", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Byte3 = _t, Byte2 = _t, Byte1 = _t, Byte0 = _t] ), #"Changed Type" = Table.TransformColumnTypes( Source, {{"Byte3", Int16.Type}, {"Byte2", Int16.Type}, {"Byte1", Int16.Type}, {"Byte0", Int16.Type}} ), BLOB = Binary.Combine( Table.AddColumn(#"Changed Type", "Custom", each #binary({[Byte0], [Byte0], [Byte2], [Byte3]}))[ Custom ] ), Items = Binary.Split(BLOB, 4), #"Converted to Table" = Table.FromList( Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error ), #"Added Custom1" = Table.AddColumn( #"Converted to Table", "Result", each let a = Binary.ToList([Column1]), b = ((a{0} * 256 + a{1}) * 256 + a{2}) * 256 + a{3} in if b > 2147483647 then b - Number.Power(256, 4) else b, Int32.Type ) in #"Added Custom1"lbendlin I believe I now have a better understanding of the mechanics right now, but could you please explain how you obtained the text below? Specifically, how can I apply the code to my original columns?
i45WMjQwVtJRMjKFkKZQMlYnWsnUEoeEhREOCUMDEM8AjsFiFoZAtiGKmJEB2GiEWCwAThe dataset I am working with consists of approximately 3 million unique serial numbers, and for each serial number, I have the two arrays (the order of the array is critical, it's a time series). Would this process still be efficient for such a dataset?