Incremental refresh with only date column available

[Anonymous]

Hi

So I need to get incremental refresh working but this seems to be only working when RangeStart and RangeEnd is set to Date/Time.
Problem is I don't have a Date/Time column, only a date one. 

I was reading this post: How to configure Incremental Refreshing in Power BI with DateKey or Date (instead of the default Dat...

but never got it working. Where it says he changed code to: 

Table.SelectRows(#"Added Custom1", each [InvoiceDateKey] >= Date.Year(RangeStart)*10000 + Date.Month(RangeStart)*100 + Date.Day(RangeStart) and [InvoiceDateKey] < Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd))

I only get error saying

Expression.Error: The name 'Added Custom1' wasn't recognized.  Make sure it's spelled correctly.

Tried changing it to 'Added Custom' but same problem. Also checked in advanced editor that the ending would be correct:

    #"Added Custom1" = Table.SelectRows(#"Added Custom1", each [RCP_DATE] >= Date.Year(RangeStart)*10000 + Date.Month(RangeStart)*100 + Date.Day(RangeStart) and [RCP_DATE] < Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd))
in
    #"Added Custom1"

But again same error as before. 

So can anyone see what I'm doing wrong here or is there a better way to make incremental refresh working with date only column available? Maybe somehow trick the column into Date/Time column properly?

Note that I do have a masterdata date table but that doesn't help since thats not the table I need to be incrementaly refreshed..

[Anonymous]

Hi

I had to XXXX out some db names and column names since its not a school project but sensitive work data I hope this isn't an issue.

Tried this in 3 ways.

First I tried to edit the column I'm trying to incrementally refresh

let
    Source = Sql.Database("XXXX", "XXXX"),
    db_XXXX = Source{[Schema="XXXX",Item="XXXX"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(XXXX,{"XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [RCP_DATE] = "20200309"),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"RCP_DATE", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Incremental", each Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd)),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each [RCP_DATE] >= Date.Year(RangeStart)*10000 + Date.Month(RangeStart)*100 + Date.Day(RangeStart) and [RCP_DATE] < Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd))
in
    #"Filtered Rows1"

Then I tried to create a new column with this code:

let
    Source = Sql.Database("XXXX", "XXXX"),
    db_XXXX = Source{[Schema="XXXX",Item="XXXX"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(XXXX,{"XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [RCP_DATE] = "20200309"),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"RCP_DATE", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Incremental", each Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "IncrementalTest", each Table.SelectRows(#"Added Custom1", each [RCP_DATE] >= Date.Year(RangeStart)*10000 + Date.Month(RangeStart)*100 + Date.Day(RangeStart) and [RCP_DATE] < Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd)))
in
    #"Added Custom1"

Then I tried to manually edit the M code:

let
    Source = Sql.Database("XXXX", "XXXX"),
    db_XXXX = Source{[Schema="XXXX",Item="XXXX"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(XXXX,{"XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX", "XXXX"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [RCP_DATE] = "20200309"),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"RCP_DATE", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Incremental", each Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd)),
    #"Added Custom1" = Table.SelectRows(#"Added Custom1", each [RCP_DATE] >= Date.Year(RangeStart)*10000 + Date.Month(RangeStart)*100 + Date.Day(RangeStart) and [RCP_DATE] < Date.Year(RangeEnd)*10000 + Date.Month(RangeEnd)*100 + Date.Day(RangeEnd))
in
    #"Added Custom1"

all ended up with this:

Expression.Error: The name 'Added Custom1' wasn't recognized.  Make sure it's spelled correctly.

[jaideepnema]

Hi @Anonymous ,

Can you share the M Code used in your table by opening query editor-> advanced editor ?

[Anonymous · Accepted solution]

Ok I figured it out but doesn't mather since now I can't see "Native query" which means incremental wouldnt work either way.

[mkcongrove1]

But what did you do? I was easily able to change the parameters and code for only date. Did you stop after that because of the break in query folding?