DataFormat.Error when connecting to folder of Excel workbooks

I need to make a report based on data in a shared folder that contains Excel workbooks for each week. All the workbooks are structured identically (same tabs, headers, etc). Each workbook contains several tabs, but the data I need is on the same tab in each workbook. I have no control over these files so I can't restructure, change file type, etc. I tried to connect to the folder, and when I combine the binaries, I choose the tab that I need. The data appears in the preview but there are a lot of null values, and ultimately when I attempt to filter out the nulls, I get an error in row 48 with "Error" in each cell. The error is "DataFormat.Error: Invalid cell value '#N/A'." Is there a way that I can get around this error? TIA :)

Null values

Errors