Hello,
I recently got access to an Oracle DB and it's connected now to Power BI. The problem is that 3 columns in the database are binary columns and I can't view their data.
| COLUMN_NAME | DATA_TYPE | COMMENTS |
| DATA_ARRAY1 | BLOB | Binary data |
| DATA_ARRAY2 | BLOB | Binary data |
| FILE | BLOB | Binary data |
And this is how it appears on Power Query:
And here is what I have when I click on the Binary:
I got some insights from The DB Admin and I am quoting him in the following:
- "The binary data in DATA_ARRAY1 and DATA_ARRAY2 are integer arrays with 3700 elements. Defined as: int data1[3700], int data2[3700] they are not an array type in the database.they are BLOB."
- He used a C# code to break that binary image into the correct format. (Not sure if we will need it to decode the data)
- "The binary image will have to be broken into 32 bit lengths for each element of the array. The binary image has all elements for the entire array just all stuck together. If you had a hex editor (or binary) and looked at that binary image you could see the individual elements of the array."
- "If power BI has the ability to allow you to save that binary image into a file then you could open that file up with a hex editor and see how the data is arranged. I think an integer array is saved in the image in order. element 0 of the array should be bits 1 - 32. and element 1 should be bits 33 - 64, etc.."
I haven't worked with Binary Data before and am not familiar with this type of manipulation. I am seeking your help either to solve the problem or to understand the process better. Any assistance you can provide in guiding me through the steps or informing me about the necessary information I should get from the DB Admin would be greatly appreciated.
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.
That text is just representing the sample data I used
By the way, I was going at this way too cute. There are much simpler ways of achieving your goal
...
Items = Binary.Split(BLOB,4),
#"Converted to Table" = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Result2", each let b=BinaryFormat.Record([val = BinaryFormat.SignedInteger32])in b([Column1])[val],Int32.Type)
in
#"Added Custom"
You can even skip the Table conversion and use List transforms directly at this point.
...
Items = List.Transform(Binary.Split(BLOB,4),each BinaryFormat.Record([val = BinaryFormat.SignedInteger32])(_)[val])
in
Items
Next step would be to use List.Zip to do a side merge between the three blobs, and finally you would convert the list of lists to a table.
This is all very low level stuff so it should have a good performance. Worst case you can use a Binary.Buffer on your blobs.
After checking with the database admin, it turns out that the data in the BLOBs is LE (little endian). or BE (big endian).So, following your direction, I changed the line ( for all, DATA_ARRAY1,DATA_ARRAY2, CFG_FILE):
List.Transform(
Binary.Split([DATA_ARRAY1],4),
each BinaryFormat.Record([val = BinaryFormat.SignedInteger32])(_)[val])
To be:
List.Transform(
Binary.Split([DATA_ARRAY1], 4),
each BinaryFormat.ByteOrder(
BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
ByteOrder.LittleEndian
)(_)[val]
)
And it's working perfectly now! I can see the correct numbers. But there remain three issues:
1) It's essential to identify to which serial number does the array belong so I need the serial number to be in the result (the seconds will be nice to have, it will reset to 0 for every new serial number):
2) I am not sure what's the best way to store the data becuase considering that I have around 3 millions unique serial numbers, each with 3 arrays with 1000 rows each, this could reach a billion data point if I follow the structure in the above image. ( I can ask this question in another post if that's better)
3) I also noticed that the code returns 3700 elements but I was expecting only 1087 elements (rows). So I asked him about this and he said that the 3700 is the the maximum elements of the array. There is DATA_POINTS column in the database that tells =exactly how many elements of data are stored in each array for DATA_ARRAY1 and DATA_ARRAY2 for every serial number. So for the sample serial number we have, the number of data points is 1087. How can I incorporate this in the code?
Thanks @lbendlin, here is the revised code where I also added an index column (Seconds) that resets with every new serial number:
let
Source =....,
GSDATA = .......,
Table1 = .........,
#"Kept First Rows" = Table.FirstN(Table1, 10),
#"Removed Other Columns" = Table.SelectColumns(
#"Kept First Rows",
{"SERIAL_NUM", "TEST_DATE", "DATA_ARRAY1", "DATA_ARRAY2", "CFG_FILE", "DATA_POINTS"}
),
#"Added Custom3" = Table.AddColumn(
#"Removed Other Columns",
"ALL",
each Table.AddIndexColumn(
Table.Combine(
List.Transform(
List.Zip(
{
List.FirstN(
List.Transform(
Binary.Split([CFG_FILE], 4),
each BinaryFormat.ByteOrder(
BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
ByteOrder.LittleEndian
)(_)[val]
),
[DATA_POINTS]
),
List.FirstN(
List.Transform(
Binary.Split([DATA_ARRAY1], 4),
each BinaryFormat.ByteOrder(
BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
ByteOrder.LittleEndian
)(_)[val]
),
[DATA_POINTS]
),
List.FirstN(
List.Transform(
Binary.Split([DATA_ARRAY2], 4),
each BinaryFormat.ByteOrder(
BinaryFormat.Record([val = BinaryFormat.SignedInteger32]),
ByteOrder.LittleEndian
)(_)[val]
),
[DATA_POINTS]
)
}
),
(x) => Table.FromRows({x}, {"CFG", "DATA1", "DATA2"})
)
),
"Secondes",
0,
1
)
),
#"Expanded ALL" = Table.ExpandTableColumn(#"Added Custom3", "ALL", {"CFG", "DATA1", "DATA2", "Secondes"}, {"CFG", "DATA1", "DATA2", "Secondes"})
in
#"Expanded ALL"
Result:
Helpful resources
FabCon Global Hackathon
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
