topic Re: Using a binary sample file for custom functions in Dataflow 2 gen in Dataflow

Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Thu, 10 Oct 2024 05:24:42 GMT

Hello!

I'm using dataflow 2 gen in order to optimize the updating of queries with custom functions.

For the function I have a sample file (for transforming other files) that must be in binary format (according to https://learn.microsoft.com/en-us/power-query/custom-function).

For some reason, when publishing the dataflow in a query with sample file two more steps are being added (From Value, Remove Columns):

let

Source

#"Filtered rows" = Table.SelectRows(Source, each Text.Contains([Name], "001.Sample.xlsx")),

#"Remove other columns" = Table.SelectColumns(#"Filtered rows", {"Content"}),

Transitions = #"Remove other columns"{0}[Content],

#"From Value" = Table.FromValue(Transitions ),

#"Remove Columns" = Table.RemoveColumns(#"From Value", Table.ColumnsOfType(#"From Value", {type table, type record, type list, type nullable binary, type binary, type function}))

#"Remove Columns"

The publishing is successful but the updating fails:

Mashup Exception Expression Error: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: Can not convert a Table type into a Binary type. Details: Reason = Expression.Error;Value = #table({}, {});Microsoft.Data.Mashup.Error.Context = User.

I have checked several posts with similar problems but haven't managed to solve mine one.

Ask you for help me with using of functions in a data flow.

Re: Using a binary sample file for custom functions in Dataflow 2 gen

lbendlin — Thu, 10 Oct 2024 12:06:14 GMT

Is "Transitions" of one of these types?

type table, type record, type list, type nullable binary, type binary, type function

Looks like they only accept tables of basic types for further processing.

Re: Using a binary sample file for custom functions in Dataflow 2 gen

miguel — Thu, 10 Oct 2024 13:43:03 GMT

Can you check if any of the queries in your customer function logic (sample file, sample transform file or anything else) have staging enabled? If any of them do, then disable staging on them

Re: Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Fri, 11 Oct 2024 11:56:25 GMT

Transitions is the step with a binary excel file.
I also have read that only tables of basic types are allowed, but in several other posts people seem to manage to find the way of processing binary files.

Re: Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Fri, 11 Oct 2024 12:15:12 GMT

As you said, I disabled staging on all files included in the function (sample file, sample transform file). Now steps From Value and Remove Columns are not being added but another error occurred while updating:

Mashup Exception Expression Error: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: Couldn't insert table., InnerException: Can not convert a Table type into a Text type.

Re: Using a binary sample file for custom functions in Dataflow 2 gen

miguel — Fri, 11 Oct 2024 13:21:06 GMT

Go through the refresh history and see in which table / query that error is raised: that should tell you a query that perhaps you have setup as a table that should probably be a text or how a function expects a text but gets passed a table value instead

Re: Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Mon, 14 Oct 2024 07:37:58 GMT

The error is raised in the query where should be transformed files.

This query must be a table as I call the custom function there and it transforms the files by a sample file parameter.

Re: Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Mon, 14 Oct 2024 10:53:06 GMT

After I disabled staging in the query with resulting tables, an error occurred in the query with an example of file transforming:

Mashup Exception Data Format Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataFormat.Error: Error when replacing table contents with new data in version: #{0}., InnerException: Could not be converted to a number., basic error: Not it was possible to convert to a number. Details: Reason = DataFormat.Error;Message = Failed to convert to a number.;Detail = Ost.5;Message.Format = Could not be converted to a number.;ErrorCode = 10041;Microsoft.Data.Mashup.Error.Context = User

Re: Using a binary sample file for custom functions in Dataflow 2 gen

miguel — Mon, 14 Oct 2024 10:48:59 GMT

This seems to be an error different to the previous one.

Do you think that you could share the M script of the relevant queries so we can better understand how to reproduce the issue or get the same results?

Re: Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Mon, 14 Oct 2024 12:46:16 GMT

I translated the steps but other names I left in the original language

1. Transforming File Sample

let

Source = Excel.Workbook(#"Parameter Sample File", null, true),

Transitions = Source {[Item = "СМГ", Kind = "Table"]}[Data],

#"Filtered rows" = Table.SelectRows(Transitions , each ([ИД primavera] <> null)),

#"Other columns with undone folding" = Table.UnpivotOtherColumns(#"Filtered rows", {"ИД primavera", "Шифр проекта", "Наименование работ", "Работы критического пути", "Статус"}, "Атрибут", "Значение"),

#"Split columns by separator" = Table.SplitColumn(#"Other columns with undone folding", "Атрибут", Splitter.SplitTextByDelimiter("/"), {"Атрибут.1", "Атрибут.2"}),

#"Trim text" = Table.TransformColumns(#"Split columns by separator", {{"ИД primavera", each Text.Trim(Text.From(_)), type nullable text}, {"Шифр проекта", each Text.Trim(Text.From(_)), type nullable text}, {"Наименование работ", each Text.Trim(Text.From(_)), type nullable text}, {"Работы критического пути", each Text.Trim(Text.From(_)), type nullable text}, {"Статус", each Text.Trim(Text.From(_)), type nullable text}, {"Атрибут.2", each Text.Trim(Text.From(_)), type nullable text}}),

#"Changed column type" = Table.TransformColumnTypes(#"Trim text", {{"Атрибут.1", type date}, {"Значение", type number}}),

#"Renamed columns" = Table.RenameColumns(#"Changed column type", {{"Атрибут.1", "Дата"}, {"Атрибут.2", "Смена"}}),

#"Uppercase text" = Table.TransformColumns(#"Renamed columns", {{"ИД primavera", Text.Upper, type text}, {"Шифр проекта", Text.Upper, type text}, {"Статус", Text.Upper, type text}}),

#"Remove other columns" = Table.SelectColumns(#"Uppercase text", {"ИД primavera", "Шифр проекта", "Наименование работ", "Работы критического пути", "Статус", "Дата", "Смена", "Значение"})

#"Remove other columns"

2. Resulting Tables

let

Source

#"Filtered rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "---")),

#"Filtered rows 1" = Table.SelectRows(#"Filtered rows", each Text.Contains([Folder Path], #"Отчетная_дата")),

#"Added a custom object" = Table.AddColumn(#"Filtered rows 1", "Пользовательское", each Text.AfterDelimiter([Folder Path], "/", 8)),

#"Split columns by separator" = Table.SplitColumn(Table.TransformColumnTypes(#"Added a custom object", {{"Пользовательское", type text}}), "Пользовательское", Splitter.SplitTextByDelimiter("/"), {"Пользовательское.1", "Пользовательское.2", "Пользовательское.3", "Пользовательское.4"}),

#"Added a custom object 1" = Table.TransformColumnTypes(Table.AddColumn(#"Split columns by separator", "Дата СМГ", each [Пользовательское.3] & "." & [Пользовательское.2] & "." & [Пользовательское.1]), {{"Дата СМГ", type date}}),

#"Added a custom object 2" = Table.AddColumn(#"Added a custom object 1", "Месяц СМГ", each Date.StartOfMonth([Дата СМГ])),

#"Remove other columns" = Table.SelectColumns(#"Added a custom object 2", {"Content", "Name", "Folder Path", "Дата СМГ", "Месяц СМГ"}),

#"Changed column type" = Table.TransformColumnTypes(#"Remove other columns", {{"Месяц СМГ", type date}, {"Дата СМГ", type date}}),

#"A custom function called" = Table.AddColumn(#"Changed column type", "A custom function called", each #"Function"([Content])),

#"Expanded: A custom function called" = Table.ExpandTableColumn(#"A custom function called", "A custom function called", {"ИД primavera", "Шифр проекта", "Наименование работ", "Работы критического пути", "Статус", "Дата", "Смена", "Значение"}, {"ИД primavera", "Шифр проекта", "Наименование работ", "Работы критического пути", "Статус", "Дата", "Смена", "Значение"}),

#"Removed errors" = Table.RemoveRowsWithErrors(#"Expanded: A custom function called", {"Дата", "Значение"}),

#"Filtered rows" = Table.SelectRows(#"Removed errors", each ([ИД primavera] <> "НЕ_ДЛЯ_ОТЧЕТА") and ([Статус] = "ПЛАН" or [Статус] = "ФАКТ") and ([Шифр проекта] <> "НЕ_ДЛЯ_ОТЧЕТА") and ([Дата] <> "Наличие") and ([Смена] <> "ТЗ")),

#"Added a custom object 3" = Table.AddColumn(#"Filtered rows", "Уникальный ИД", each Text.Combine({[ИД primavera], [Шифр проекта]}, "||"), type text),

#"Combined queries" = Table.NestedJoin(#"Added a custom object 3", {"Уникальный ИД"}, Спр_работы, {"Уникальный ИД"}, "Спр_работы", JoinKind.LeftOuter),

#"Expanded: Спр_работы" = Table.ExpandTableColumn(#"Combined queries", "Спр_работы", {"ИД_вехи_классификация_ТЗ_L1", "ИД_вехи_классификация_ТЗ_L2", "ИД_вехи_классификация_ТЗ_L3", "Раздел РД", "Объект/название объекта", "Участок", "Шифр объекта", "Основной_объект", "Подрядчик", "Проектный офис", "Вид работ", "Вес по виду работ", "Вид работ первый уровень", "Вид работ третий уровень"}, {"ИД_вехи_классификация_ТЗ_L1", "ИД_вехи_классификация_ТЗ_L2", "ИД_вехи_классификация_ТЗ_L3", "Раздел РД", "Объект/название объекта", "Участок", "Шифр объекта", "Основной_объект", "Подрядчик", "Проектный офис", "Вид работ", "Вес по виду работ", "Вид работ первый уровень", "Вид работ третий уровень"}),

#"Replaced value" = Table.ReplaceValue(#"Expanded: Спр_работы", null, "ПО_не_выбран", Replacer.ReplaceValue, {"Проектный офис"}),

#"Added a custom object 4" = Table.TransformColumnTypes(Table.AddColumn(#"Replaced value", "ТЗ_В_СУТКИ", each [Вес по виду работ] * [Значение]), {{"ТЗ_В_СУТКИ", type number}}),

#"Changed column type 1" = Table.TransformColumnTypes(#"Added a custom object 4", {{"ИД primavera", type text}, {"Шифр проекта", type text}, {"Наименование работ", type text}, {"Работы критического пути", type text}, {"Статус", type text}, {"Смена", type text}, {"Дата", type date}, {"Значение", type number}}),

#"Removed columns" = Table.RemoveColumns(#"Changed column type 1", {"Content", "Name"})

#"Removed columns"

Re: Using a binary sample file for custom functions in Dataflow 2 gen

miguel — Mon, 14 Oct 2024 14:00:43 GMT

Could you also share the m script for #"Function"?

also, where exactly are you seeing the error when in the power query editor? It should raise an error in the authoring time

Re: Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Mon, 14 Oct 2024 14:22:56 GMT

Function:

(#"Parameter Sample File" as binary) => let

Source= Excel.Workbook(#"Parameter Sample File", null, true),

Transitions = Source{[Item = "СМГ", Kind = "Table"]}[Data],

#"Filtered rows" = Table.SelectRows(Transitions , each ([ИД primavera] <> null)),

#"Split columns by separator" = Table.SplitColumn(#"Other columns with undone folding", "Атрибут", Splitter.SplitTextByDelimiter("/"), {"Атрибут.1", "Атрибут.2"}),

#"Changed column type" = Table.TransformColumnTypes(#"Trim text", {{"Атрибут.1", type date}, {"Значение", type number}}),

#"Renamed columns" = Table.RenameColumns(#"Changed column type", {{"Атрибут.1", "Дата"}, {"Атрибут.2", "Смена"}}),

#"Remove other columns"

Re: Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Mon, 14 Oct 2024 14:26:16 GMT

The error is raised while updating:

Re: Using a binary sample file for custom functions in Dataflow 2 gen

miguel — Mon, 14 Oct 2024 14:59:46 GMT

You do not see any queries with errors whilst inside the power query editor ?
You should be able to see the queries with errors. You could hit the "refresh preview" button in the ribbon to make sure that the queries are evaluated using the latest state and see if they're getting any errors.

if the errors are only happening during the refresh, then that would mean that the issue is not with the functions but rather with the destination configuration that you have. I'd recommend removing the data destination to rule out the issue on the destination configuration and doing a refresh - if you don't get any issues then the new error is at the destination configuration and it's a completely new issue than the original that was reported

Re: Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Tue, 15 Oct 2024 07:38:42 GMT

No error occurs in the queries. I used the Save rows option for each query to check it and the result was an empty table.
After I removed the destination configuration it updated successfully.
But when I turned on staging in the query with the resulting tables the error ocurred (while updating, not in the query):

Do I get it right that staging is not a necessary option and it is for updating optimisation?
Сan I disable it without heavy losses for the dataflow?

Other dataflows are connected to Lakehouse destinations (each dataflow to its Lakehouse).
Could you tell how to create a Lakehouse and correctly connect the dataflow to it?
In the Lakehouse that I connected to the queries of dataflow I found the error:

The SQL analytics endpoint could not be created.

Re: Using a binary sample file for custom functions in Dataflow 2 gen

miguel — Tue, 15 Oct 2024 11:28:17 GMT

The conversion error more than likely happens because you have a column that contains a table type whereas the destination expects a text or the conversion that you have for such column couldn't convert a table into text

Re: Using a binary sample file for custom functions in Dataflow 2 gen

perepelena — Wed, 16 Oct 2024 11:24:46 GMT

I thoroughly examined the query I assigned the Lakehouse destionation in, there are columns only of three types: text, dates and numbers, and I do not see any conversion of table type into text type. I created a new empty Lakehouse but anyway until I assign the destination the query updates successfully, then the error occures: Can not convert a Table type into a Text type.
Ask you to help me with that question.

Re: Using a binary sample file for custom functions in Dataflow 2 gen

miguel — Wed, 16 Oct 2024 13:41:23 GMT

I'm unable to repro your scenario. If you have a way in which I could repro it with a given sample and / or public data source, please do share it.

otherwise, I'd recommend raising a support ticket so the support team can help troubleshoot and look at this scenario much deeper. Below is the link to open a support ticket:

https://support.fabric.microsoft.com/support