Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 DateTime) - Reporting/Analytics Made easy with FourMoo and Power BI

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

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

4 Replies

  • jaideepnema's avatar
    jaideepnema
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi Anonymous ,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        mkcongrove1
        Icon for Advocate I rankAdvocate I
        2 years ago

        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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.