Skip to main content

Power BI

New

Allow columns of binary type in Power BI Dataflow (Basic) entities

Vote (10) Share
Pär Adeen's profile image

Pär Adeen on 24 Mar 2019 17:02:29

Please also allow column of binary types in Power BI Dataflow as Basic Entities.
As for now, binary column values are allowed for logical basic entities, i.e. unless you enable loading.
While this is partly good, it breaks my possibilities to user Power BI Dataflow.

But as soon as I materializes the entity by enabling load (and press Done), Power Query automatically inserts the following line into my Power Query M-code:
Table.RemoveColumns(Source, Table.ColumnsOfType(#"Remove columns", {type table, type record, type list, type nullable binary, type binary, type function}))

I can agree of type table, record, list and functions, but i can't agree of type binary and type nullable binary

Rational: I'm having most of my DB Natural Keys as binary UUID columns. To be able to replace these binary keys with a Surrogate Key I need to materialize the entity (enable load). And as soon as I materialize the entity, the Natural Key (binary column) is gone. Well... Not directly, It only happens when I press Done (by inserting the row above). The effect of this is that the refresh fails and the column is gone the next time I edit the entity

I know there are discussions about not using binary columns as keys in your database, but I have no influence of the application database what-so-ever. I also have no possibility to create views or Store Procedures in the source database.

Yes I know i can write native queries and convert the binary values, but that will bread the query folding. Well, breaking the query folding might not do much harm, but it does. Query folding is a requirement for the Power BI Dataflow incremental refresh to work.

So no matter how I do, I always fall back to not being able to use Power BI Dataflow for data sources with binary keys