If you are getting the Data source reference is not valid while trying to create a pivot table in Microsoft Excel, see this post for the fix.

The following reasons may play a part in triggering this FileType error: The file format could indeed be the cause for FileType selected not being supported by Teams, Excel, etc.

How to Recover a Microsoft Excel File That Is Not in a Recognizable Format. If Microsoft Excel fails to recognize a file in the XLSX or XLS format, the data contained in the file could be corrupted.