Incremental refresh doesn't work

[1123]

Hi, I hope you're all doing good and could help me to handle this issue...

I have Power BI Premium account (not sure if that's relevant though) and in Power BI desktop, I created a query defined RangeStart as 2019-01-01 01:00:00 and RangeEnd as 2019-01-01 01:01:00:

I filtered the related dataset in the query:

I refreshed the report and it showed only the data which has CREATE_DATE between 2019-01-01 01:00:00 and 2019-01-01 01:01:00, and that makes perfect sense. Then I set the incremental refresh in the report:

Then I refreshed the report. It reloaded the data but nothing has changed. It kept showing me the data which has CREATE_DATE between 2019-01-01 01:00:00 and 2019-01-01 01:01:00. My first question Q1: Is this normal?

Then I published the report in Power BI services and did refreshing there and again it kept showing me the data which has CREATE_DATE between 2019-01-01 01:00:00 and 2019-01-01 01:01:00. My second question Q2: How can I fix that?

In everywhere it's said that the dates that you choose for RangeStart and RangeEnd don't matter. However, in my case they seem like only things that matter!

What do you think?

Note: I tried to check whether the query is foldable or not but I am a bit confused, some relates visibility of "View Native Query" with foldability. And while that's greyed when I check the "Source" step, it's visible when I check other steps.

       

[v-kelly-msft]

Hi @1123 ，

Q1: It's normal.

Q2: You can change the parameter of RangeStart and RangeEnd to change the time period you need for refresh.

Dates that you choose for RangeStart and RangeEnd of course matter, here is the reference.

https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mwegener]

Hi @1123 ,

Q1: Yes, this is normal.

Q2: What source are you using? 

[1123]

Thanks for the reflection @mwegener !

My data source is an Oracle SQL database.

[mwegener]

Hi @1123 

is your source a SQL query or a table?

[1123]

Hi @mwegener; It's a table

[mwegener]

Hi @1123 

can you share the M code from the advanced editor?

https://docs.microsoft.com/en-us/power-bi/desktop-query-overview#advanced-editor

Has the data been refreshed in the service without an error message?

[1123]

Hi @mwegener 

Here the query:

let
Source = Table.Combine({Query1, Query2, Query3, Query4}),
#"Removed Columns" = Table.RemoveColumns(Source,{"col", "col2", "col3"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [CREATE_DATE] >= RangeStart and [CREATE_DATE] < RangeEnd)
in
#"Filtered Rows"

I haven't received any error during refresh

[mwegener]

Hi @1123 ,

Your source consists of 4 further queries, how are these structured?

[1123]

Hi @mwegener 

They all have similar structure:

let
Source = Oracle.Database("***DATABASECREDENTIALS***", [HierarchicalNavigation=true]),
TABLEX = Source{[Schema="TABLEX"]}[Data],
TABLE1 = TABLEX{[Name="TABLE2"]}[Data],
#"Added Custom" = Table.AddColumn(TABLE1, "Col9", each "TABLEX")
in
#"Added Custom"

[mwegener]

Hi @1123,

I would also add the filtering to the subqueries to ensure that the query folding works and that the data is filtered directly at the source.

However, I see no reason why the values should not come from the source.

[mwegener]

Can you share a screenshot of the refresh history?