BUG::on-prem DB ingestion of table with column of varbinary datatype

[Element115]

STEPS

SQL Server DDL:

CREATE TABLE DBNAME.dbo.MUP_Status (
	statusId int IDENTITY(0,1) NOT NULL,
	display varchar(100) COLLATE Latin1_General_CI_AS NULL,
	lastAccessed datetime2(0) NULL,
	currentLayout varchar(100) COLLATE Latin1_General_CI_AS NULL,
	MUP_screenshot varbinary(MAX) NULL
);

0__create new DF Gen2

1__connect to on-prem DB:  this is the M code after expanding the Data column and removing all the other columns not from the DB table:

let
  Source = Sql.Database("server", "DBNAME"),
  #"Navigation 1" = Source{[Schema = "dbo", Item = "MUP_Status"]}[Data],
  #"From Value" = Table.FromValue(#"Navigation 1"),
  #"From Value 1" = Table.FromValue(#"From Value"),
  #"From Value 2" = Table.FromValue(#"From Value 1"),
  #"From Value 3" = Table.FromValue(#"From Value 2"),
  #"Remove Columns" = Table.RemoveColumns(#"From Value 3", Table.ColumnsOfType(#"From Value 3", {type table, type record, type list, type nullable binary, type binary, type function}))
in
  #"Remove Columns"

2__the last line, #"Remove Columns", automatically removes the varbinary column from the source without me having selected this column!  In fact, I do need this column to stay and be persisted to the lakehouse.  

3__delete the line #"Remove Columns"

4__click Publish, no Data destination set (this DF is supposed to be the input into a second DF in the chain, so no need to persist it to a user created lakehouse, but 'Enable staging' is on).

5__reopen the DF after publishing is complete

6__the deleted line #"Remove Columns" is back, automatically generated by Power Query!

This is not what is intended.  Why? What gives? How to stop this from occurring?

[Element115 · Accepted solution]

@v-cboorla-msft @frithjof_v  finally found 2 solutions:

1__explicitly specify the column type to nullable binary like so:

  #"Changed column type" = Table.TransformColumnTypes(#"From Value 1", {{"MUP_screenshot", type nullable binary}})
in
  #"Changed column type"

OR

2__insert these lines of code to check for errors in the binary column, and if no error, just return the table with all the columns; this too prevents the PQ Editor from automatically inserting a remove column step:

  #"From Value 1" = Table.FromValue(#"From Value"),
  #"Kept errors" = Table.SelectRowsWithErrors(#"From Value 1"),
  #"final-step" = if Table.RowCount(#"Kept errors") > 0 then #table({"Error count"},{{#"Kept errors"}}) else #"From Value 1"
in
  #"final-step"

source: Re: Dataflow - How can I stop automatic steps bei... - Page 2 - Microsoft Fabric Community

[v-cboorla-msft]

Hi @Element115 

Glad that your query got resolved.

Please continue using Fabric Community for any help regarding your queries.

[frithjof_v]

I don't have experience with binary column, but I think you need to parse the content of the column into text type (ABC) or something like that before publishing the dataflow gen2, because I don't think you can save the column with binary type in Power Query.

Perhaps the solution in this article can help you (create a new column with Text.FromBinary)

https://community.fabric.microsoft.com/t5/Desktop/How-to-convert-Binary-data-from-DB-to-Power-BI/m-p...

Or, maybe try to just change the data type of the binary column to ABC type directly.

[Element115]

Well, there is a Binary type that appears in the Power Query editor of a DF Gen2.  The thing is that the column is automatically removed upon publishing and that should not happen.  Instead, if it's something that should not be done, at the very least an error message should be displayed.

In any case, you got a point.  I had no choice but to convert the column binary to text.  This column contains JPEG images.  I still have to test to see if my Power BI report will display these images, but since there is another bug with Data Factory, I cannot proceed yet.

[v-cboorla-msft]

Hi @Element115 

Thanks for using Microsoft Fabric Community.

Apologies for the inconvenience that you are facing here.

I tried to repro the scenario and got the similar issue. Please find the screenshots below.

So, this might require a deeper investigation from our engineering team and they can guide you better.

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support
Please provide the ticket number here as we can keep an eye on it.

Hope this is helpful. Please let me know incase of further queries.

[Element115]

SR# 2401250040013953