I am trying to combine all excel files within a certain folder with Power Query. However, I am getting the following error "Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] The key didn't match any rows in the table.. ".
I know that I am getting this error because all of my tab names are different in every file. However, there are more than 500 files and I don't want to change the tab name for all of these. I know for a fact this is the issue, because I changed the tab names for three of the excel files to be the same, and the Power Query is pulling those in. I believe there is only one tab in each file, therefore, I was curious if I could teach the Power Query to grab the tab, regardless of the tab name, so it can be combined.
This will combine every tab of every excel files in a directory using powerquery.
Paste it into home ... advanced editor ...
let Source = Folder.Files("C:\subdirectory\directory"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetFileData", each Excel.Workbook([Content],true)),
#"Expanded GetFileData" = Table.ExpandTableColumn(#"Added Custom", "GetFileData", {"Data", "Hidden", "Item", "Kind", "Name"}, {"Data", "Hidden", "Item", "Kind", "Sheet"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded GetFileData",{"Content", "Hidden", "Item", "Kind"}),
List = List.Union(List.Transform(#"Removed Columns"[Data], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", List,List)
in #"Expanded Data"