Forum Discussion
Binary data Oracle data type
Hi,
Desktop is the latest version 2.43.4647.541 64-bit (February, 2017)
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.
It cannot be expanded using the down arrows, as would be seen with a file.
To simply the example, two cols are chosen.
Close and apply the query. Look at the Feilds list in Report view, CATEGORY (alpha numeric type) is there, AFFECTED_RC is not.
This is direcy query mode. Its the same behaviour in import mode, which can't be used anyway because of volume of data.
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.
Does this help clarify?
You can apply a transformation for the Binary column as Text.FromBinary.
= Table.AddColumn(YourTable, "Text_CATEGORY", each Text.FromBinary([CATEGORY]))
Unfortunately no, it gives a warning about adding a step that results in a query that is not supported
in DriectQuery mode.
Am trying something like this -
let
Source = Oracle.Database("odsprd", [HierarchicalNavigation=true]),
DTMRT_ADS = Source{[Schema="DTMRT_ADS"]}[Data],
TRILL_CALL_REQ1 = DTMRT_ADS{[Name="TRILL_CALL_REQ"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(TRILL_CALL_REQ1,{"AFFECTED_RC", "CATEGORY", "ID", "ZCR_LOCATION"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "AFFECTED_RC", "AFFECTED_RC - Copy"),
#"Added Custom" = Table.AddColumn(#"Duplicated Column", "Custom", each Text.FromBinary([AFFECTED_RC]))
in
#"Added Custom"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.
This does not feel like a solution, maybe I got lucky with the one piece of data where this will work.
What if you do Table.TransformColumnTypes and choose a different type then binary?
#"Added Custom" = Table.TransformColumnTypes( #"Duplicated Column",{ "AFFECTED_RC",type text})Same as before - adding a step that results in a query that is not supported in DriectQuery mode.
Its not an error as such though its not much use either.
Copying the col does this (different field used for example) -
Can see the data type has been detected as Alphanumeric in the new field even if contetns not visible in query view.
Doing similar on the association Location dimension will allow a join between the two new copied columns.