Merge Column from Folder Connector Query is Slowing Down PQ Performance

[dbalkin777]

Greetings. One of my data sources is seval excel files combined using the folder connector (i.e., sample file --> final query with all excel files appended). Performance is fine in that final query, but I'm trying to merge that query (left anti join) with another query in order to remove some overlapping records. I don't know why, but merging between the two tables is untenably slow. In this merge step, I can see Power Query loading all of the files from the combined query, but it is running painfully slow. Any ideas on why this is running so slowly would be greatly appreciated.

[Omid_Motamedise]

How many rows of data do you have befor merging?

[Anonymous · Accepted solution]

Hi @dbalkin777 ,
Thanks for Jonvoge reply.
You can try to reduce the table size by filtering out unnecessary rows before merging. Next, create indexes on the columns you want to merge. This can help Power Query perform merge operations more efficiently. Of course, you also need to make sure that the data types of the columns you want to merge are consistent. Mismatched data types can slow down the merge process.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Jonvoge]

Hi dbalkin777

Not sure why it is running slower.

But perhaps you could put that part of the query into a dataflow, and then connect to the Dataflow from your Semantic Model (if you are indeed building a semantic model?), and merge the other data here?

Might be worth a shot for increasing performance.

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

[dbalkin777]

Thanks for your reponse! That idea occurred to me too but I can't set up the gateway needed to go that route due to work restrictions.