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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MS1986
Regular Visitor

Query slows down after using Table.First Power Query

Hi All,

 

I'm new to the Forum so sorry if this is posted in a wrong place.

 

I tried to find a solution on Google, but unfortunately, it seems that no one has stumbled upon a similar issue. 

 

I started using Power Query only recently, so I'm still learning. Anyway, I am trying to build an automated reporting for vendor expense map for my company. I have 4 data source files which are transformed and merged into one query with mappings applied. 

 

Problem starts when I try to Merge my Left Table with a mapping table which contains duplicates. In order to avoid duplicated lines (which is not expected for a final result) I use Table.First function and then the issue starts. Up until the function, everything works smoothly. When I apply the function a whole query slows down and takes ages to calculate. Any ideas on how to fix this would be much appreciated.

 

 

1 ACCEPTED SOLUTION

Thank you very much! I did exactly this last night trying to figure out the way. This helped and now the query works way smoother, I also included Table.Buffer for all  source tables and switched off the backroundupdates for queries. Now extracting 180k records takes approx 3-5mins. 

 

 

View solution in original post

3 REPLIES 3
ImkeF
Super User
Super User

Remove the duplicates from your (right) lookup-table before merging it: Faster merge and no need to select first values afterwards.

(Create an intermediate table if you need the lookup-table in its original state in the data model)

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

Thank you very much! I did exactly this last night trying to figure out the way. This helped and now the query works way smoother, I also included Table.Buffer for all  source tables and switched off the backroundupdates for queries. Now extracting 180k records takes approx 3-5mins. 

 

 

Good to hear! 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.