The #1 reason query folding breaks on Redshift views is writing a custom SQL statement in the connection dialog (e.g., SELECT * FROM my_view).
Here is the solution to fix the folding and the M code.
The Solution: Do Not Write SQL
To ensure Query Folding works, you must connect to the server first and then navigate to the view using the Power Query interface. Do not put anything in the "SQL Statement" box when you first click "Get Data".
The Correct M Code Structure
Below is the standard pattern that preserves query folding. You can paste this into your Advanced Editor (replacing your specific details).
let
// Step 1: Connect to the Database level only (No SQL Query inside this function)
Source = AmazonRedshift.Database("your-cluster-url.redshift.amazonaws.com", "your_database_name"),
// Step 2: Navigate to your Schema (e.g., 'public')
// Note: Power Query creates these navigation steps automatically if you click through the list
public = Source{[Name="public"]}[Data],
// Step 3: Select your View
// Because we navigated to it, Power BI treats it like a table and can fold queries
MyView_View = public{[Name="your_view_name"]}[Data],
// Step 4: Apply the Incremental Refresh Filter
// Because the previous steps were navigation, this step will fold into a WHERE clause
#"Filtered Rows" = Table.SelectRows(MyView_View, each [YourDateColumn] >= RangeStart and [YourDateColumn] < RangeEnd)
in
#"Filtered Rows"
How to Verify It Is Working
Delete your current query if you used a custom SQL statement.
Go to Get Data > Amazon Redshift.
Enter the Server and Database, but leave the SQL Statement (Optional) box EMPTY.
Click OK. A navigator window will appear.
Expand your schema and select your View checkbox. Click Transform Data.
Now, filter your date column using the RangeStart and RangeEnd parameters.
Right-click the "Filtered Rows" step. The "View Native Query" option should now be clickable (black), not grayed out.
What if it still doesn't fold?
If you use the method above and it still doesn't fold, check these two things:
Privacy Levels: Go to File > Options and settings > Options > Current File > Privacy. select "Ignore the Privacy Levels...". Sometimes merging data from different sources (or parameters) blocks folding if privacy levels aren't compatible.
Value.NativeQuery (The Force Method): If the navigation method fails, you can force the SQL context using Value.NativeQuery.
Force Folding M Code:
let
Source = AmazonRedshift.Database("your-url", "your-db"),
// Force the query to be recognized as a foldable source
RunQuery = Value.NativeQuery(
Source,
"SELECT * FROM public.your_view_name",
null,
[EnableFolding=true]
),
#"Filtered Rows" = Table.SelectRows(RunQuery, each [Date] >= RangeStart and [Date] < RangeEnd)
in
#"Filtered Rows"
If this explanation and solution resolve your issue, please like and accept the solution.
