Limit Dimension Data based on Fact Table in Power Query

Ok. Your current query setup won't allow query folding.

Can you try connecting without using an explicit SQL statement please?

Connect to the DB again, and leave the SQL statement blank:

Just hit OK, then select your dim_employee table from the table list and import.

Assuming this goes as planned, select the [EMPNO] column in your new dimension table and filter just one value. This should be fast to implement and sets us up a new step with the correct structure.

Edit that new step in the formula bar, so it uses our streaming list filter, something like this:

= Table.SelectRows(
    previousStepName,
    each List.Contains( List.Buffer( factTableName[EMPNO] ), [EMPNO] )
)

This setup should allow PQ to stream the [EMPNO] values from your Excel source into the SQL native query sent to the SQL source for your dim_employee table.

Pete 

Hi Anonymous ,

If you right-click your #"Filtered Rows" step, is 'Native Query' now selectable, or is it still greyed-out?

If it's still greyed out, try changing one of the column data types to the same type, but not a text column. For example, change a date type column to date type, or a decimal type column to decimal. This is purely to force PQ to re-evaluate the query and hopefully force it to generate a native query to source.

If native query IS selectable, then you're probably at the limit of optimisation. The Oracle Data Access Client (ODAC) requirement isn't the best in this regard.

Pete

Pete