danhendo888
Board Regular
- Joined
- Jul 15, 2019
- Messages
- 154
- Office Version
- 365
- Platform
- Windows
I have two folders, A and B, which currently have 4 Excel files in each. So 8 files total. Each file is roughly 50,000 rows, 21 columns, each cell has data (not formulas) and is between 10 to 15mb (each).
There will be more excel files added as each file represents one calendar month.
In power query, I will import folder A so that when I expand the column with the tables in editor, it automatically joins all 4 files. I'll remove columns, rename columns etc and I end up with about 15 to 20 query steps.
This is query 1
I will do the same for folder B. This is query 2. I will rename, remove columns etc. Then from this query, I will append with query 1 (so that all data from folders A and B are appended) and load to data model.
So query 2 ends up having 600,000 rows. Both the queries are in one excel file of course and the file is currently 8mb.
Is it normal for a refresh of the query 2 to be slow? (1minute 10 seconds)
And also when I load to pivot table, it is also quite slow (also 1 minute 10 seconds)
FWIW
The data in each file is not in table format.
There are two sheets in each file, I only use one of the sheets and not the other.
Before I loaded the queries into the data model, I had them loaded as a connection only and made pivot tables for testing purposes. I have since deleted those pivot tables and currently I have only one data model pivot table.
Only query 2 is loaded to the data model. Query 1 is just a connection.
There will be more excel files added as each file represents one calendar month.
In power query, I will import folder A so that when I expand the column with the tables in editor, it automatically joins all 4 files. I'll remove columns, rename columns etc and I end up with about 15 to 20 query steps.
This is query 1
I will do the same for folder B. This is query 2. I will rename, remove columns etc. Then from this query, I will append with query 1 (so that all data from folders A and B are appended) and load to data model.
So query 2 ends up having 600,000 rows. Both the queries are in one excel file of course and the file is currently 8mb.
Is it normal for a refresh of the query 2 to be slow? (1minute 10 seconds)
And also when I load to pivot table, it is also quite slow (also 1 minute 10 seconds)
FWIW
The data in each file is not in table format.
There are two sheets in each file, I only use one of the sheets and not the other.
Before I loaded the queries into the data model, I had them loaded as a connection only and made pivot tables for testing purposes. I have since deleted those pivot tables and currently I have only one data model pivot table.
Only query 2 is loaded to the data model. Query 1 is just a connection.