Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Incremental refresh with only date column available

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..

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Solution Sage
Solution Sage

Hi @Anonymous ,

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

Anonymous
Not applicable

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

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.