Load to data model incredibly slow

[halifaxious]

I have an Excel file that uses both Power Query and Power Pivot. Without user input, the file is 2.6MB.

• The data model has 13 tables, all based on queries of tables internal to the file-- i.e. we're not connecting to anything remote.
• None of the tables has >200 rows and most have between 20 and 50.
• I have timed the queries and they all run and load in less than 1 second except for one. It takes 0.1 seconds to run and about 20 seconds to load. When I removed it from the data model, the query ran quickly. When I added it back, loading was still slow.

The problem table has 6 columns:

ID, integer

FY, integer

Date, date

VA, currency

VF, currency

None of them are calculated. The table has no measures or relationships. Why does it load so slowly?

[#2]

@halifaxious 

If on copy of the file you remove all queries but this one, is it still slow?

[#3]

I have the same issue.
I duplicated the extremely slow query and removed the "Add to data model".
The duplicated query now runs in seconds.
The original query, with loading to data model, takes around a minute.

Why is the adding to the data model so slow?

[#4]

@DannyvdVuurst 

In general it shall be no big difference. Without the file it's hard to say what could be the reason. At least be sure in Power Query table data types are declared for all columns, i.e. you have no columns with "any" data type.