Binary data Oracle data type

Question

Hello,&nbsp;We a have a problem accessing Binary data in Oracle. This is not image/blob data. Can see the columns while in query view. When the query is run the columns disappear from the report view and cannot be used. This happens in both import and direct query mode.&nbsp;How to deal with Binary data types in Power BI?&nbsp;Regards&nbsp;Ger

v-qiuyu-msft · Answer

Hi ger,
&nbsp;
Which kind of Binary data type did you store on&nbsp;Oracle database? What do you mean "&nbsp;Can see the columns while in query view. When the query is run the columns disappear from the report view and cannot be used. This happens in both import and direct query mode."? Can you share some screenshots to clarify&nbsp;the issue?
&nbsp;
By the way, make sure you are using the latest desktop version 2.43.4647.541.
&nbsp;
Best Regards,Qiuyun Yu

ger · Answer

Hi,&nbsp;Desktop is the latest version 2.43.4647.541 64-bit (February, 2017)&nbsp;The database is the backend of an off-the-shelf product.This is the Query Editor view of one of the tables. The Binary data type is highlighted.&nbsp;&nbsp;It cannot be expanded using the down arrows, as would be seen with a file.&nbsp;To simply the example, two cols are chosen.&nbsp;Close and apply the query. Look at the Feilds list in Report view, CATEGORY (alpha numeric type) is there, AFFECTED_RC is not.&nbsp;&nbsp;This is direcy query mode. Its the same behaviour in import mode, which can't be used anyway because of volume of data.&nbsp;The data in the fields, and there are many like this. The content is not an image or other binary type, is more like a alphanumneric string.&nbsp;&nbsp;Does this help clarify?&nbsp;&nbsp;

hugoberry · Answer

You can apply a transformation for the Binary column as Text.FromBinary.&nbsp;= Table.AddColumn(YourTable, "Text_CATEGORY", each Text.FromBinary([CATEGORY]))&nbsp;&nbsp;

ger · Answer

hugoberry&nbsp;Unfortunately no, it gives a warning about adding a step that results in a query that is not supportedin DriectQuery mode.&nbsp;Am trying something like this -&nbsp;let&nbsp;&nbsp;&nbsp; Source = Oracle.Database("odsprd", [HierarchicalNavigation=true]),&nbsp;&nbsp;&nbsp; DTMRT_ADS = Source{[Schema="DTMRT_ADS"]}[Data],&nbsp;&nbsp;&nbsp; TRILL_CALL_REQ1 = DTMRT_ADS{[Name="TRILL_CALL_REQ"]}[Data],&nbsp;&nbsp;&nbsp; #"Removed Other Columns" = Table.SelectColumns(TRILL_CALL_REQ1,{"AFFECTED_RC", "CATEGORY", "ID", "ZCR_LOCATION"}),&nbsp;&nbsp;&nbsp; #"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "AFFECTED_RC", "AFFECTED_RC - Copy"),&nbsp;&nbsp;&nbsp; #"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each Text.FromBinary([AFFECTED_RC]))in&nbsp;&nbsp;&nbsp; #"Added Custom"&nbsp;&nbsp;Interestingly, if the offending column is simply copied and no manual transform applied, it seems to work. I suspect the problem centres on trying to find the data type from the binary.&nbsp;This does not feel like a solution, maybe I got lucky with the one piece of data where this will work.&nbsp;

hugoberry · Answer

What if you do Table.TransformColumnTypes and choose a different type then binary?#"Added Custom" = Table.TransformColumnTypes( #"Duplicated Column",{ "AFFECTED_RC",type text})

Forum Discussion

Binary data Oracle data type

9 Replies

Related content

Data Extraction from Oracle

Accessing Binary Columns from Oracle Database

Binary data

Sharepoint Data with Oracle Data

Oracle connection error