Power Query Join Transform on a Very Large Table

[Shawn Keene]

I'm having trouble with getting data from a very large Oracle SQL table.  It seems like Query Folding may not be occurring with my left inner join, so the server is returning all 519 million rows of data from the main table, rather than just the records I want (records from the last 30 days).  But before I can filter by date range, I have to join the main table via the Date_ID column with the Date table (which itself has 18k rows), expand the actual date column, and then filter by it.

When I'm in the editor, I seem to remember joins like this would preview quickly, I thought it was because it was only using a small subset (say 200 rows) of data, or maybe it was using query folding so the server handled the join and where-clause, and only returned back the small set I wanted.  But now it's clear that isn't happening.

[#2]

Hello Shawn,
Here is a good reference for your problem: https://www.thebiccountant.com/2019/04/18/query-folding-joinkind-inner-gotcha-power-bi-power-query/

Since you are dealing with a very large data, on the beginning of your large table Keep the first ( or last since you want the latest data) 1k rows, then perform the merge with the date table. after performing the merge, expand the date table, and perform your filter.

after that, remove the "Keep Rows" step, and the query folding should be fine again.

[#3]

I appreciate the response, however the root issue was addressed in a update for the Oracle connector, in the 1903 release. So it's now a moot point.

I'd also like to thank the development team, who responded to my in-app feedback from the frown icon, they emailed me directly to let me know the fix was in the pipeline and when the release date would be. Very awesome service.