Forum Discussion
Using a binary sample file for custom functions in Dataflow 2 gen
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 fo...
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
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.
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 = UserThis 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?
I translated the steps but other names I left in the original language
1. Transforming File Sample
letSource = 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 TablesletSource#"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"
