Incremental Refresh from TE3 Gets Nebulous Type Conversion Error

[Jerid421]

Hello,

I’ve been trying to implement IR on a model (which I’ve done many times).  I have other models that operate with almost the same exact code (expressions and M Source Queries).   But I am trying to convert this one over to IR and I keep getting this nebulous error:

“Cannot convert value ‘<ccon></ccon>’ of type Text to type Date.”

The only thing that remedies it (seemingly) is when I add a step to my M Source Expression that converts DATE_WID (Date key as an integer “YYYYMMDD”) to an integer.  This column is already explicitly cast as an Integer in the Snowflake source and is set as Integer in the model.  While adding this data type conversion step allows the query to refresh, it breaks the query folding, and then the WHERE clause is omitted from the partition’s query against the source.  Which is why I think it seemingly “fixes” the conversion issue, because the RangeStart and RangeEnd parameters aren’t then being used (nothing to fail on).  The reason I think this issue is with those parameters is because the error refers to “Text to type Date” and nothing I am doing is converting anything from type Text to type Date.   And because one of the times it errored out, I got a different message that included this:

let _AS_Query_ = let RangeStart=DateTime.FromText("20231031T00:00:00"), RangeEnd=DateTime.FromText("20231101T00:00:00") in PolicyBased_36686F87_B3FA_4760_A613_9C6D47A37170, _AS_Table_ = Table.FromValue(_AS_Query_J, _AS_Compact_ = Table.RemoveColumns(_AS_Table , Table.ColumnsOfType(_AS_Table , {type table, type record, type list})), _AS_Effective_ = Table.TransformColumnNames(_AS_Compact , Text.Clean) in AS Effective

This seems to be converting the start and end dates from a string to Datetime before it is passed to the RangeStart and RangeEnd expressions.  But this is being done by Tabular Editor / The Service / the Refresh Policy?  Right?  I have no control over this. 
 
I’ve been banging my head on the desk over this for a day and half!  

Things I have done already:  Check for NULLS in the source for DATE_WID, deleted the model and rebuilt, test the source query in PBI Desktop

[Jerid421]

So, since I posted this, I have gotten the model to refresh.  Strangely, the thing that "unstuck" it was to refresh a full yearly or quarterly partition, before refreshing any of the more recent daily partitions.  These daily partitions didn't have any data in the source, which I think might be what caused the issue.  And I don't think it was the actual partition refresh that was failing but the post-refresh calculation that occurs.  You see, my dimDate table is DAX-based and it looks at the fact tables to determine the data ranges it needs to have. 

------------------------------------------------------------
--
-- Configuration
--
------------------------------------------------------------
--All the information needed to understand this table can be found here: 
--https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
--and here: https://github.com/sql-bi/DaxDateTemplate
------------------------------------------------------------


VAR TodayReference = TODAY ()
VAR CalendarFirstDate =
DATEVALUE (
    FORMAT (
        MIN (
            'factPayments'[DATE_WID]
        ),
        "####-##-##"
    )
)

VAR CalendarLastDate =
DATEVALUE (
    FORMAT (
        MAX (
            'factPayments'[DATE_WID]
        ),
        "####-##-##"
    )
)

VAR FirstYear = YEAR ( CalendarFirstDate )
VAR LastYear = YEAR ( CalendarLastDate )
VAR FiscalCalendarFirstMonth = 1 -- For Fiscal 52-53 weeks (start depends on rules) and Gregorian (starts on the first of the month) 

So, I think when those daily partitions try to refresh, and there is no data in them, it makes the post-refresh calculation associated with dimDate fail.  Which fails the whole partition refresh.  

[Jerid421 · Accepted solution]

This verifies what the problem is.  By running this simple DAX query in TE3, I can reproduce the exact same error I was getting:

So, the answer here is, when employing this DAX-based dimDate table and refreshing an "empty" model (metadata-data only / freshly-deployed), to be sure to:

• refresh a partition that you are sure has data in the source, first, before the other partitions
• include some error-trapping code in the DAX that accounts for instances where it might return an empty string (BLANK()) 

[lbendlin]

Would you have had this issue with an externally sourced Calendar table ?  🙂

[lbendlin]

I've said it many times and I'll say it again.  Creating calendar tables in DAX or Power Query is futile. Those tables are immutable and should be sourced further upstream.  You should also never combine Incremental Refresh with Auto Date/Time - exactly to avoid these kind of post refresh computations and to avoid refreshes of related partitions.

[Jerid421]

Can you tell me what you meant with "with Auto Date/Time"?  I don't have any Auto Date/Time columns in this model (or any model ever, for that matter).   😀 

[lbendlin]

That's good. You can check the partitions. If you see any rogue ones you know that Auto Date/Time is enabled.

[Jerid421]

Well,  I vehemently disagree.  I know that a DAX-based calendar table can cause some issues sometimes, but the juice is worth the squeeze (IMHO). 

• they are light-weight (data as code), they are dynamic (always the exact records that you need, not some SQL-based giant that needs stored procs to update it every couple years)
• their logic is included in source control with their associated model (not in the source control for the data warehouse)
• their logic can quickly and easily be changed by the BI professional with no arguing or waiting on data engineers to add / fix things
• that logic / features can differ from model to model (a table in the DW can't unless you give it a hundred columns)
• having the calendar logic contained within the Power BI environment alongside other model logic can simplify management and troubleshooting, as everything is managed in a unified manner.

But I certainly respect your opinion and your approach. 

[lbendlin]

Can you show  a sanitized version of your M query?

[Jerid421]

let
  // Extract the prefix from Envirobase
  EnvPrefix = Text.BeforeDelimiter(Envirobase, "_"),
  // Determine Role based on prefix
  RoleValue =
    if EnvPrefix = "DEV" then
      "DEV_SYS_ADMIN"
    else if EnvPrefix = "TST" then
      "TST_SYS_ADMIN"
    else if EnvPrefix = "PROD" then
      "PROD_SYS_ADMIN"
    //if an invalid parameter value is provided, this unknown role will cause it to fail
    else
      "UNKNOWN_ROLE",
  Source = Snowflake.Databases(
    "XXXXXXXXXTENANT.east-us-2.azure.snowflakecomputing.com",
    "YYYYYYYYYWAREHOUSE_PROFILING",
    [Role = RoleValue]
  ),
  //Navigate to the Snowflake database that corresponds to the enviroment the model is currently in (e.g. DEV to DEV, TST to TST)
  //This is set with the Envirobase parameter (made-up word; there is only 1 enviroment in Snowflake with databases named to mimic different environments)
  #"Environment/Database" = Source{[Name = Envirobase, Kind = "Database"]}[Data],
  //Navigate to the RETAILPAYMENTS schema of whichever database / environment was provided
  RETAILPAYMENTS_Schema = #"Environment/Database"{[Name = "ZZZZZZZZZZSchema", Kind = "Schema"]}[Data],
  Data = RETAILPAYMENTS_Schema{[Name = "vfactPayments_IR", Kind = "View"]}[Data],
  //Implements Incremental Refresh on table
  ApplyIncrementalRefresh = Table.SelectRows(
    Data, 
    each [DATE_WID]
      >= ConvertDatetimeToInt(#"RangeStart") and [DATE_WID]
      < ConvertDatetimeToInt(#"RangeEnd")
  )
in
  ApplyIncrementalRefresh

[lbendlin]

By the way, you can run Incremental Refresh with RangeStart and RangeEnd natively defined as date integers rather than DateTime.

[Jerid421]

When you say "natively", you mean without having to employ a function to convert them in the M Query? 

[lbendlin]

correct.  Read the Supported Data Sources section.  Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn