Forum Discussion
Convert YYYYMMDD String to DateTime w/o breaking query folding
Thank you for the reply. Yes, I have already tried the Table.TransformColumnTypes approach along with Table.AddColumn. Both break query folding because I'm changing a text field to a date field. Right now, I've switched back to odbc.query and I am pushing as much into the SELECT query as possible to leverage the service on the initial step. It's not query folding but it may well improve performance, we'll see.
Anonymous and MattGell.
I agree and marked this as new, again...
I'm also having this problem, but has managed to solve it two ways.
1) If your DB contains a date/calendar table with a date or datetime column, you can join in the datetime column from the data/calendar table. Luckely, most DW/DB has a calendar table available since this is the reason the dates are in text/int format, so in most cases this will work out fine.
The join you will do by creating a none loading PQ entity that you merge with your target table. In the code below I merge the table called Date into a Table called Table1 that are missing a datetime column
let
Source = Sql.Databases(Server),
DB= Source{[Name=Database]}[Data],
Table1 = DB{[Schema="dbo",Item="Table1]}[Data],
Merge1 = Table.NestedJoin(Table1, {"TimeId"}, Date, {"DateId"}, "Calendar", JoinKind.LeftOuter),
Expand1 = Table.ExpandTableColumn(Merge1, "Calendar", {"DateColumn"}, {"DateColumn"}),
ChangeType1 = Table.TransformColumnTypes(Expand1,{{"DateColumn", type datetime}})
in
ChangeType1
And yes, this will fold like this:
select [_].[TimeId] as [TimeId],
convert(datetime2, [_].[DateColumn]) as [DateColumn]
from
(
select [$Outer].[TimeId],
[$Inner].[DateColumn]
from
(
select [_].[TimeId]
from [dbo].[Table1] as [_]
) as [$Outer]
left outer join [dbo].[Date] as [$Inner] on ([$Outer].[TimeId] = [$Inner].[DateId])
) as [_]
2) This requires that you have the possibility to add a view to the database. In that case you just create a view that will do the conversion to datetime. Yes I know, this was not wat was requested, but it might be a possibility.
P.S. The codes above was slightly modified to make this more clear. I hope I didn't introduce some error in that process.
If MS reads this. Please give a real query folding safe solution for converting a text or int date column to date/datetime.
Thanks for the reply, alas my DB does not have a calander table - I created my calander table in PBI desktop.
MS please work on a solution to what must surely be a very common issue.
I guess you understand, but I mention it anyway since I know this is an important request. You can either ask the DBA to implement a simple DATE and DateInt/DateText referens table for you to use with all your joins.
If I remember correctly, this table can be in in any schema in the same DB, but it can't be a table in other DB, e.g. the temp DB. I Once tried using PQ code with Native SQL to create a global temporary table in the DB (using CREATE TABLE ##DateTimeTable...), but this didn't work if I remeber correctly. The reason had something to do with the posibilities to do PQ joins from different DB (since the temp DB is a DB of it's own)
But if you could get your DBA to add the Date reference table you are good to go
Alternativ you ask your DBA to add a view something like below (I'm not native SQL speeking) :
CREATE VIEW Table1_WithDateTime AS
SELECT
convert(...... datetime...) AS TimeColumn,
*
FROM Table1
And why shall the DBA do this to you.... Bacause the load/bandwithUsage of his SQL server will be less if you are able to do incremential refreshes.
And as a third alternative for the DBA. He might also be able to add a new column to the table. And if he uses cumputed column capabilities he will not allocate extra space, although indexes migth be a bigger issue to ashive