Issue getting date/time format for incremental refresh set up

[robrob97]

Hi all, I have a singular dataset with source from starburst odbc. There are many millions of rows a month (20 million) and I would rather set up incremental refresh so it's just pulling in the latest data daily and I can have at least 4-5 months backlook in here. 

However, I have followed the steps from documentation and still have errors. https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure#convert-dateti...

1. I tried to just make my date field a date/time field:

• The format changed, but still when I try to filter the field, it only recognises it as a date. And so I cannot use RangeStart and RangeEnd to filter it by. 
• 

2. I tried using the conversion steps to make my date an integer. But I get an error message when I try to add = Table.SelectRows(#"Reordered Column OrderDateKey", each [OrderDateKey] > DateKey(RangeStart) and [OrderDateKey] <= DateKey(RangeEnd)) to my query. Also how do I add this alongside the other information already in my query formula? 

I have tried searching everywhere and videos, but nowhere is giving me different advice. So I am kind of stumped what to try...

[Anonymous · Accepted solution]

Hi @robrob97 

When you filter the datt, it should not filter in power bi desktop, you need to filter it at power query.

When you set the paramaters(RangeStart and RangeEnd), you then filter the date/time column in power query.

Then it can use the paramaters.

And you can refer to the following link.

Incremental Refresh and Hybrid tables in Power BI: Load Changes Only - RADACAD

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.