Skip to main content
Microsoft Idea

Power BI

Needs Votes

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

Vote (15) 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

Comments (2)
Pär Adeen's profile image Profile Picture

Andreu fil on 20 Apr 2024 00:44:01

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

Good information, thankssehackear.top

Pär Adeen's profile image Profile Picture

James Isherwood on 20 Jul 2023 14:41:21

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

I want to point out that this is an essential and basic functionality as a programmer that should be included. The ability to use queries modularly (as functions) to pass files to other queries is a core workflow I need.It reduces query length and complexity while encouraging reusability and modularity.For example:All Files (pre-processes files in an online repository for consumption - naming, errors, type, etc)Query A (inherits All Files, makes needed transformation)Query B (inherits All Files, makes needed transformation)Query C .....This doesn't work if the binary and record fields are forcefully removed from queries without the ability to disable this column-type handling.I would really love to see this patched for dataflows and datasets as soon as possible. Thanks.