Forum Discussion
xlnm filter duplicate "error" when refreshing data
Hi,
I am uploading several excel workbooks from a single folder. Each workbook has a worksheet for each person with his/her name.
I am getting this error xlnm filterdatabase, it seems this does happen when a filter is applied in the source, even if it has already been removed. I was getting duplicates from this and the data was not uploading since it was incompatible with my one to many relationship in a table. Just filtered/removed these xlnm filterdatabase in the power query. Is there a more efficient way to resolve this?
In the image below you have "Paulo", and a non existant worksheet "xlnm filterdatabase"
campelliann
Did you figure out which specific excel file caused that error, just test out.Once you have find the problematic excel, copy all the cells and paste them in an empty new sheet then work with the new sheet, remove the old one.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
campelliann
Did you figure out which specific excel file caused that error, just test out.Once you have find the problematic excel, copy all the cells and paste them in an empty new sheet then work with the new sheet, remove the old one.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.I got a better one.
When you uploading multiple files in a document, first step is to add a column with "Excel.Workbook".
Then normally when we expand the custom column, what we need is the "data" item.
This time select "name", "data", "kind" all together. You will see the rows are increased with these hidden tables named "_xlmn._FilterDatabase", and in the "kind" column, these rows are tagged with "DefinedName".
All you need to do is filter the "kind" column, leave "sheet" along, and then delete all the columns except "data". Don't worry even delete the "kind" column will not cancel the filter. Now, you get a neat source of data without duplication.
The following steps are as usual. Problem solved for good.
thank you!
Create a new copy is the faster solution for now.
Or you can write a vba context to show all table names in the file, and then delete the "xlmn filterdatabase".
But there is no guarentee that after you have done any modification to the file, a new filterdatabase won't be created.
Besides, process the files one by one is so inefficient.
I'm still looking for any way that can prevent the filterdatabase being produced, or to delete dbs in multiple files at one time.
Pls let me know if you find one. I'll do the same.
