topic Re: BUG::on-prem DB ingestion of table with column of varbinary datatype in Dataflow

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

Element115 — Wed, 24 Jan 2024 23:59:38 GMT

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?

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

v-cboorla-msft — Thu, 25 Jan 2024 18:11:51 GMT

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.

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

frithjof_v — Fri, 26 Jan 2024 06:13:43 GMT

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/575884

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

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

Element115 — Fri, 26 Jan 2024 13:59:41 GMT

SR# 2401250040013953

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

Element115 — Fri, 26 Jan 2024 14:06:43 GMT

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.

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

Element115 — Mon, 29 Jan 2024 21:15:18 GMT

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"

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

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

v-cboorla-msft — Wed, 31 Jan 2024 04:53:32 GMT

Hi Element115

Glad that your query got resolved.

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