Couldn't refresh the entity because of an internal error

[tan_thiamhuat]

There was a problem refreshing the dataflow: 'Couldn't refresh the entity because of an internal error'. Error code: Canceled. (Request ID: b4f3c163-1186-4bae-a7ff-3c44eed08113).

How would I able to trace that internal error?

The M-code is below:

let

    // Step 1: Load existing Dim_RateCode

    ExistingRateCode = Dim_RateCode,

    // Step 2: Load sftpdata

    Source = SFTP_Data,

    // Step 4: Select only Channel and SubChannel columns

    SelectedColumns = Table.SelectColumns(Source, {"RateCode", "RateCodeName"}),

    // Step 5: Remove duplicates & NA

    RemoveDuplicates = Table.Distinct(SelectedColumns),

    RemoveNulls = Table.SelectRows(RemoveDuplicates, each Record.FieldValues(_) <> {null, null}),

    // Remove special characters from RateCodeName

    CleanedText = Table.TransformColumns(RemoveNulls, {{"RateCodeName", each Text.Replace(_, "�", ""), type text}}),

    // to deal with Special Characters

    // Add JoinKey using the custom function

    WithJoinKey = Table.AddColumn(CleanedText, "JoinKey", each fx_GenerateJoinKey({[RateCode], [RateCodeName]})),

    ExistingWithJoinKey = Table.AddColumn(ExistingRateCode, "JoinKey", each fx_GenerateJoinKey({[RateCode], [RateCodeName]})),

    // Extract only keys from existing

    ExistingKeys = Table.SelectColumns(ExistingWithJoinKey, {"JoinKey"}),

    // Anti-join: get new rows not in existing

    RateCode_ToInsert = Table.SelectRows(WithJoinKey, each not List.Contains(ExistingKeys[JoinKey], [JoinKey])),

    // Final output without join key

    RateCodeToInsert = Table.RemoveColumns(RateCode_ToInsert, {"JoinKey"})     

in

    RateCodeToInsert

Is it due to that CleanedText line which I want to remove that special character? because before I insert that line, I remember it does not have issue. But even after removing that line, the problem still persists.

[tan_thiamhuat]

at times, I often get this:

NavigationQuery: Error Code: Mashup Exception Expression Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: Failed to insert a table., InnerException: We cannot convert a value of type Table to type Text., Underlying error: We cannot convert a value of type Table to type Text. Details: Reason = Expression.Error;ErrorCode = Lakehouse036;Message = We cannot convert a value of type Table to type Text.;Message.Format = We cannot convert a value of type #{0} to type #{1}.;Message.Parameters = {"Table", "Text"};ErrorCode = 10276;Microsoft.Data.Mashup.Error.Context = User (Request ID: 21e77fef-e9c6-41e2-8057-7d84e37de97e).   any clues what is all that about?

[v-saisrao-msft]

Hi @tan_thiamhuat,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @tan_thiamhuat,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi @tan_thiamhuat,

Thank you for reaching out to the Microsoft Fabric Forum Community. 

Based on the error messages you’ve shared, especially the ones referencing type conversion issues like "We cannot convert a value of type Table to type Text" and "Failed to insert a table", the problem likely lies in how the data types are being handled in your transformations. These errors often occur when a transformation expects scalar values such as text or numbers but instead receives a table or record which seems possible in the case where you're using a custom function like fx_GenerateJoinKey.
Check if any of the fields like RateCode or RateCodeName contain nulls or aren't explicitly converted to text, this could trigger a mashup error during the refresh even if it previews fine. Also ensure the function itself is resilient against nulls or unexpected data types, as they tend to surface more during scheduled refreshes than during manual query edits. 

Additionally, issues with mashup documents can also stem from schema mismatches or invalid transformations, for instance when you're trying to apply text functions like Text.Replace to a column that hasn’t been properly typed as text. If your RateCodeName column contains any binary, list, or nested table values, the transformation may silently fail or behave inconsistently during refresh. Explicitly casting columns to the correct types before applying transformations can help prevent these issues. 

Please refer to the community links for a better understanding: 

Solved: Re: Couldn't refresh the entity because of an issu... - Microsoft Fabric Community 

Solved: Re: 104100 Couldn't refresh the entity because of ... - Microsoft Fabric Community 

On-premise and VNET data gateway considerations for data destinations in Dataflow Gen2 - Microsoft F...

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.