Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Merge queries causes performance issue

hi all,

 

I have 2 tables. One table with 20+ million records and one excel with 70 records.

I want to merge the 2 tables because in need to apply logic based upon the 2 tables file. I merged the 2 tables with left outer join, but this gives really slow performance in refreshes. Anyone knows a good workaround or alternative?

 

thanks!

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

I would try Remove Duplicates on the Excel file (even if you know there are none) so that PQ knows there are none. If your key columns are DEFINITELY sorted, then you can use Table.Join (not nested join) and the final parameter JoinAlgorithm.SortMerge.

 

--Nate

View solution in original post

Anonymous
Not applicable

Very awesome suggestion to try, @Anonymous .

 

I reviewed this case and please allow me to offer some additional thoughts:

  1. Incremental Refresh: If you're refreshing in Power BI Service, consider implementing an incremental refresh policy for your large table. This approach limits the amount of data processed and refreshed to only what's new or changed, significantly reducing refresh times. For more details on setting this up, see Configure incremental refresh.

  2. Use DirectQuery Mode: If applicable, using DirectQuery mode for your large dataset can improve performance by executing queries directly on the source data without the need to load it into Power BI. This can be particularly effective for large datasets, but it's important to understand the trade-offs, such as dependency on the source system's performance. More on DirectQuery can be found here: Use DirectQuery in Power BI Desktop.

 

Hope above could help.

 

Best Regards,

Stephen Tao

 

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Very awesome suggestion to try, @Anonymous .

 

I reviewed this case and please allow me to offer some additional thoughts:

  1. Incremental Refresh: If you're refreshing in Power BI Service, consider implementing an incremental refresh policy for your large table. This approach limits the amount of data processed and refreshed to only what's new or changed, significantly reducing refresh times. For more details on setting this up, see Configure incremental refresh.

  2. Use DirectQuery Mode: If applicable, using DirectQuery mode for your large dataset can improve performance by executing queries directly on the source data without the need to load it into Power BI. This can be particularly effective for large datasets, but it's important to understand the trade-offs, such as dependency on the source system's performance. More on DirectQuery can be found here: Use DirectQuery in Power BI Desktop.

 

Hope above could help.

 

Best Regards,

Stephen Tao

 

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

Anonymous
Not applicable

I would try Remove Duplicates on the Excel file (even if you know there are none) so that PQ knows there are none. If your key columns are DEFINITELY sorted, then you can use Table.Join (not nested join) and the final parameter JoinAlgorithm.SortMerge.

 

--Nate

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors