Troubleshooting slow Power Query

[natemoss1218]

Hi everyone!

I use power query for a template for my job when doing data imports for new clients. A recent client has a massive amount of data and its causing my template to lag. I notice it when scrolling from left to right and if I filter the query. Filtering a column renders the template almost unusable.

The template has two tabs and a total of four queries.

The first tab has three queries to clean and prep the data (data comes from 3 separate reports provided by the client. total of 30 columns and a little over 70,000 rows on this tab).

The second tab references the first three queries and combines and appends all the queries into one table to import into our system. The query on this tab (the one we use to import into our system) references the other three queries to combine and append the data into one table and remove duplicate records. Total of 12 columns and about 71,000 rows

I've done everything I can to make the queries as efficient as possible and reducing the steps to as few as I can. I know it has nothing to do with my computer hardware (I spent several thousand on this rig and built it myself).

I just want to see if this is simply a lot of data for PQ to handle or if there is an issue with something I have built perhaps. I wish I could provide the file, but it is almost solely comprised of personal information

I don't have any kind of ego so criticisms, thoughts, and suggestions are most welcome here.

 

[VBA learner ITG]

I had the same issue when connecting to an .xlsx file with 160K rows of data. I found that converting the file where your query is stored/run in to .XLSB does help.

Perhaps if possible convert the Import file to from an .xlsx file to Text/CSV pre inport?