Understanding The “External table is not in the expected format” Error In Power Query In Power BI And Excel

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.

Let’s see a simple example. Say you have a folder with two files: one is an Excel file called Date.xlsx and one is a CSV file called Date.csv.

Here’s the M code for a Power Query query that connects to the Excel file and reads the data from a table in it:

let
  Source     = Excel.Workbook(File.Contents("C:\MyFolder\Date.xlsx"), null, true), 
  Date_Table = Source{[Item = "Date", Kind = "Table"]}[Data]
in
  Date_Table

Now, if you change the file path in this query – and only the file path – to point at the CSV file instead like so:

let
  Source     = Excel.Workbook(File.Contents("C:\MyFolder\Date.csv"), null, true), 
  Date_Table = Source{[Item = "Date", Kind = "Table"]}[Data]
in
  Date_Table

…you will get the “external table is not in the expected format” error shown above. This is because your code is using the Excel.Workbook M function, which is used to import data from Excel workbooks, to connect to a file that is a CSV file and not an Excel workbook. The way to fix it is to use the appropriate function, in this case Csv.Document, to access the file like so:

let
  Source = Csv.Document(
    File.Contents("C:\MyFolder\Date.csv"), 
    [Delimiter = ",", Columns = 4, Encoding = 65001, QuoteStyle = QuoteStyle.None]
  ), 
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
in
  #"Promoted Headers"

To be honest, if making this change is beyond your Power Query skills and you’re sure you’re trying to connect to the right file, you’re better off creating a completely new query rather than editing the query you already have.

Another common scenario where you might encounter this error is when you’re importing data from all the files in a folder and one of the files isn’t in the correct format. For example, let’s say you have a folder with three Excel files in and you use the Folder data source to import all the data from all three files:

Since all three files are Excel files the Folder option will work:

However, if you take a CSV file and drop it into the folder like so:

Then you’ll get the same error in Power Query:

Apart from deleting the CSV file you have another option to solve this problem in this case: filtering the folder so you only try to get data from the .xlsx files and no other file type. To do this, click on the step that is called “Source”. When you do this you’ll see that the step returns a table containing all the files in the folder you’re pointing at:

You’ll see that the table in this step contains a column called Extension which contains the file extension for each file. If you filter this table – which will insert a new step at this point in the query, which is ok – by clicking on the down arrow in the Extension column, delselecting the (Select All) option and selecting “.xlsx” so the table only contains .xlsx files then you can avoid this problem:

If, as in this example, the rogue file happens to be the first file in the folder and you’ve selected that first file to be your “sample” file when setting up the import, then you’ll also need to go to the query called Sample File in the Queries pane and make exactly the same change there (ie click on the Source step and filter to remove any non .xlsx files).

Understanding The “A Measure Is Used In Cross Highlighting” Error In Power BI

When interacting with a Power BI report you may occasionally get the following error:

Couldn’t load the data for this visual

We can’t display this visual because a measure is used in cross highlighting. Please remove the measure or cross highlight.

What does this mean? The most important thing to explain is that you have not done anything wrong: you have, unfortunately, run into a limitation of Power BI where it can’t generate the query needed for a selection on a visual. The issue occurs in some very rare cases when Power BI needs to filter a measure using multiple fields from different tables. Since only workarounds involve changing your visuals, changing the way you interact with your report or changing the way your report behaves, it is helpful to understand the scenarios where you will encounter it.

To do this, take the following basic Power BI dataset:

There are two products in the Product table – Apples and Oranges – and two countries in the Country table – UK and France, plus some sales values in the fact table. All the data in the dataset (minus the keys) can be shown in a table like so:

Note that fields from all three tables in the dataset (the Country field from the Country table, the Product field from the Product table and the Sales field from the Sales table) are used in this visual and Sales is used as a measure.

One way to trigger this error is to select any one of the rows in the body of this table, right-click and select Exclude:

The presence of a measure plus two fields from two different tables is the key to making the error occur: if you remove either the Country or Product fields then the Exclude will work ok. You could also remove the Sales measure, but it’s likely both of these changes will result in the visual not displaying what you want it to display so you’re better off educating your users to expect to see this error if they use Exclude. Selecting the rows you want to keep and using Include instead also works:

Another way to trigger the error is to cross-highlight this table by selecting two rows from different levels in a separate matrix visual on the same page. So, for example, let’s say the same data was displayed in a matrix visual alongside the table visual on the same page:

If, in the matrix visual, you select the France row and the UK/Apples row at the same time, then the error will occur again:

If you use Edit Interactions to stop the matrix from being able to cross highlight the table then you’ll be able to stop the error:

Another possibility is to select rows that are all at the same level to cross highlight instead of selecting rows from different levels. So, for example, if you change the second matrix to be another table and select the two rows for France and the row for UK/Apples, you won’t get the error:

I’m sure there are other scenarios where this occurs (I see this is called out by OKVIZ here for some of their custom visuals) but hopefully these examples are enough to help you understand what’s going on here.