Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Accessing Binary Columns from Oracle Database

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 ...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

lbendlin's avatar
lbendlin
Super User
2 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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: