Forum Discussion

Jerid421's avatar
Jerid421
Helper II
2 years ago
Solved

Incremental Refresh from TE3 Gets Nebulous Type Conversion Error

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 tryi...
Need Help
  • Jerid421's avatar
    Jerid421
    2 years ago

    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's avatar
lbendlin
Super User
2 years ago

Can you show  a sanitized version of your M query?

Jerid421's avatar
Jerid421
Helper II
2 years ago
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