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 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):
#"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.
17 Replies
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.
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.
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
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.
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?
