Merge taking too much time and refresh is failing

[girdharimondal]

Expert,

I need to merge two table both have record around 70,00,00. Have merge them using left outer join. But referesh is just keep on going and ultimately going for time out. Both Desktop and Dataflow is giving same issue. 

Please suggest.

Thanks,

Girdhari

[v-pnaroju-msft]

Hi girdharimondal,

We are pleased to learn that your issue has been resolved. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[girdharimondal]

Thanks Everyone for your valuable response!

Was able fix the issue , it was actually a cardinality issue. I did not notice it, join was happening on M:M relation and that's why keep on exceuting . We have corrected the keys and everything is fine now

Regards,

Girdhari

[NimaiAhluwalia]

Hello @girdharimondal 

Below are a few things you can try!

Model instead of flatten

• If the intent is to bring fields from one table into another for reporting, ask: can this be a relationship instead?

• Build a star schema: one Fact table + Dimension tables with relationships. This avoids heavy merges in Power Query.

Filter and slim before you merge

• Remove unneeded columns early (folded Remove Columns).

• Filter rows down to the needed time window (folded Filter Rows).

• Avoid transforming to Text keys, use Integer or Fixed Decimal keys if possible.

Common mistakes that kill performance

• Steps before merge that break folding (e.g., custom row-level functions, some Add Column logic, unbuffered web/excel sources).

• Merging on non-matching data types (Text vs Whole Number).

• Unnecessary sorts.

[DNMAF]

Hi @girdharimondal , did you already try to buffer the two tables, that means simply adding the following step to both queries before your join step?

  Table buffered = Table.Buffer(#<your last step>)

[girdharimondal]

Thanks for your response, will check and come back.

Regards,

Girdhari

[Mauro89 · Accepted solution]

Hi @girdharimondal,

Merging 7 million rows with another large table will always be slow in Power BI. Here are couple of options you can try:

Best approach - Merge at the source:

1. Use SQL/Database views: If both tables are from the same database, create a SQL view that joins them and import that single view into Power BI
2. Push the merge to the data source: This is 100x faster than Power Query merging

If you must merge in Power BI:

1. Reduce rows before merging: Apply filters to both tables BEFORE the merge step to reduce row count
2. Remove unnecessary columns: Keep only the columns you need from each table before merging
3. Check data types: Ensure join keys are the same data type (both text or both numbers) - mismatches cause performance issues
4. Use Table.Buffer: Before the merge step, add Table.Buffer(YourTable) to load the smaller table into memory first

Alternative architecture:

1. Use DirectQuery: Instead of importing, use DirectQuery mode - the database handles the join

7M x 7M rows is beyond Power Query's best practise. You really need database-level joining.

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

[girdharimondal]

Thanks for your response, will check and come back.

Record count is 700K not 7M. It's should not be too much for power BI, I beleive. 

Regards,

Girdhari

[raisurrahman]

@girdharimondal 

To improve performance and efficiency, I recommend optimizing the right-hand table before merging. Please selectively keep only the essential columns from that table, discarding any that are not needed for the join or final result. Merging all 700K rows with excessive columns may be causing overhead.