Forum Discussion
Error occurred in appending a SQL table with an excel file
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.
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.
1 Reply
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.