Error occurred in appending a SQL table with an excel file

[johnyip]

I tried to append (or in SQL language, union) two data sources, one of them is from Azure database and another is simply an Excel file.

Everything works fine when I was preparing the steps in Power Query, but when I pressed close and apply, this error occurs:

Failed to save modifications to the server. Error returned: 'The search Text provided to function 'FIND' could not be found in given text. The current operation was cancelled because another operation in the transaction failed. '

The M script used is like the below:

The commented part is literally the definition of "Adjustment" table, it was there becasue I tried to fix the error by internalizing the adjustment table into the M script and then use that outcome to invoke Table.Combine, but in vain.

As shown, there is  no syntax error. When I pressed done, the preview can load, but if I press "Close & Apply "afterwards, shortly the said error message pops up.

[johnyip · Accepted solution]

Hi all,

I found the cause. That is because of some human errors in the data preparation steps I have made for the Excel data source.

I made one silly mistake by defining "YYYYMM" in the Excel source, while the original table's column is "YYYY MMM". I just changed the Excel source's custom column from showing "YYYYMM" to "YYYY MMM", and then everything works.

So, I think this error can be caused by data format inconsistency, when you have used FIND() in DAX (specifically, you can search for " " in "2022 Jan", but not in "202201". This is what I observed.).

Hope anyone who encountered the same problem and read this post can be assisted.