Sometimes when you’re importing data from files using Power Query in either Power BI or Excel you may encounter the following error:
DataFormat.Error: External table is not in the expected format
What causes it? TL;DR it’s because you’re trying to load data from one type of file, probably Excel (I don’t think you can get this error with any other source but I’m not sure), and actually connecting to a different type of file.
Read on for an example, a more detailed description of when you’d get the error, and how to fix it.