Tables taking too long to merge in Power Query

[Anonymous]

Hi,

I have 2 tables of approximately 1.5L records from MySQL.

I need to merge the tables based on a key(Text datatype) which is unique.

The merging takes very long time to load and never completes it. (Refer below image).

Any solution to the above problem will be really helpful.

Thanks,

Prajna

[BA_Pete]

Hi @Anonymous ,

Vijay has given you a good steer on how to speed up your merges, but I'd recommend not merging at all, if possible.

Merges are a 'whole table' operation in Power Query i.e. they require an entire table to be loaded into memory (or at least every row to be scanned at least once), so can hit memory limits for very large tables.

I would recommend just sending both tables to the data model and relating them on your unique key field. You may see an increase in your PBIX file size (as relationships have a 'size' in PBI), but the VertiPaq engine/DAX should be orders of magnitude faster than a Power Query merge.

Pete

[Vijay_A_Verma]

As first level of performance improvement, do following -

1. Remove all columns which are not required for your analysis / decision and not required for merging in botht the tables.

2. Also if you can apply filter to minimize number of rows required to do merging.

3. File - Options & settings - Options - Current File - Data load -Uncheck "Allow data previews to download in the background"

4. You can also explore possibility of using Table.Join as given here - https://blog.crossjoin.co.uk/2020/06/07/optimising-the-performance-of-power-query-merges-in-power-bi...