Re: Using a binary sample file for custom function...

perepelena · ‎10-09-2024

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}))

in

#"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.

miguel · ‎10-11-2024

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

perepelena · ‎10-14-2024

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

miguel · ‎10-14-2024

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?

perepelena · ‎10-14-2024

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", "Шифр проекта", "Наименование работ", "Работы критического пути", "Статус", "Дата", "Смена", "Значение"})

in

#"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"})

in

#"Removed columns"

miguel · ‎10-14-2024

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

perepelena · ‎10-14-2024

The error is raised while updating:

miguel · ‎10-14-2024

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

perepelena · ‎10-15-2024

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):

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.

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.

miguel · ‎10-15-2024

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

perepelena · ‎10-16-2024

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.

miguel · ‎10-16-2024

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

perepelena · ‎10-14-2024

Function:

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

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

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