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
I cross-posted this in PowerQuery channel too...
