Binary data Oracle data type

[ger]

Hello,

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.

How to deal with Binary data types in Power BI?

Regards

Ger

[steambucky]

Yes...Any news on relsoving this? Did you manage to import a Oracle Blob? Or did you figure out some sort of work-a-round?

[leed]

Hi @ger

Are you still stucking in this problem or you know how to get out of this?

Thanks,

Dung Anh

[pfalicoff]

Hello..

Any news on this, if this can be resolved?

We are trying to build our dataset, using the query builder, making relationships between tables where the PK is a binary raw type column. The problem, is Power BI, doesn't show this columns, so we can't make the relationships. We don't want to build the dataset with a predifined sql, as this is not performant.

Any help will be appreciate it.

Microsoft, never answer or help on this issue. Or even advised if this will be resolved in future versions of Desktop,

Regards

Pablo

[v-qiuyu-msft]

Hi @ger,

Which kind of Binary data type did you store on Oracle database? What do you mean " 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 the issue?

By the way, make sure you are using the latest desktop version 2.43.4647.541.

Best Regards,
Qiuyun Yu

[ger]

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?

[hugoberry]

You can apply a transformation for the Binary column as Text.FromBinary.

= Table.AddColumn(YourTable, "Text_CATEGORY", each Text.FromBinary([CATEGORY]))

[ger]

@hugoberry

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.

[hugoberry]

What if you do Table.TransformColumnTypes and choose a different type then binary?

#"Added Custom" = Table.TransformColumnTypes( #"Duplicated Column",{ "AFFECTED_RC",type text})

[ger]

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.