Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
robarivas
Post Patron
Post Patron

How to use Power Query (M) efficiently

‎02-16-2018 10:45 AM

I created "QueryZ" (using Append) that establishes a population of accounts coming from 2 tables. I want to use those accounts as a filter against other tables. So I do a Table.Join against some table (call it TableX) hoping the load process will only scan a limited number of rows from TableX. However, Power BI still seems to try to scan the entirety of TableX during the load process. Is there a best practice of how to do mergeing in Power Query (or otherwise leveraging the accounts from Query1) to prevent the load process from scanning the full TableX.

 

This issue is important to me because I happen to be dealing with massive tables so I need the tool to not evaluate excess rows unneccesarily.

Labels:
Message 1 of 3
1,965 Views
0
2 REPLIES 2
ImkeF
Community Champion
Community Champion

‎02-18-2018 10:06 AM

You might experience this bug in query folding: http://www.thebiccountant.com/2017/07/20/sql-query-folding-bug-still-alive-sucking-powerbi-powerquer...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 3 of 3
1,927 Views
0
stretcharm
Memorable Member
Memorable Member

‎02-18-2018 06:13 AM

There are some tips on this page.

https://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/

 

Also you can turn on tracing from the setting menu which may help you see what it's doing and if any changes are making it better or worse.

 

There are some buffer functions that may help.

https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buff...

 

Some posts that may have useful details

https://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables...

http://community.powerbi.com/t5/Desktop/merging-tables-very-slow-performance/td-p/122730

Message 2 of 3
1,932 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 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.

View All