Convert YYYYMMDD String to DateTime w/o breaking query folding

[Eric]

The date field in my source file is stored as a string in YYYYMMDD format.  I need to convert it to a DateTime field in order to do incremental refresh and I need to do that without breaking query folding or the incremental refresh will be extremely inefficient.  I have tried a dozen differnet ways but I have not found one that works.  Please help.

[pade]

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

[MattGell]

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.

[pade]

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

[pade]

This is marked as solved without having a solution. I switch it back to new and hope for an answer.

Please make sure that the question is to be able to change existing or create a new column with a datetime format from an integer (or string) type column with dates in the format of YYYYMMDD, this while retaining the query folding towards the data source

[v-frfei-msft]

Hi @Eric ,

Did you want to change the data type without adding a new step? It is not possible in power query as I know.

 I need to do that without breaking query folding 

---

BTW, please check this way.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDQ0MjZUitWBcAwMDZA4RiBOLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "date", "date - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"date - Copy", type date}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"date - Copy", type datetime}})
in
    #"Changed Type1"

[Eric · Accepted solution]

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.

[MattGell]

Please do not mark as solved when it it is clearly not. Its very frustrating searching for what is clearly a common problem. The issue is how to convert text to date without breaking query folding not just the simple text to date conversion. If anybody has truly solved this please post the solution.

[Renaud]

I'm also looking for a solution. All the dates in my SQL database are stored as text, not as numbers.
And I can't convert them to date without breaking the query folding (which means, for example, that I can't use those fields for incremental refresh).

I wonder if there is a way to convert without breaking query folding. There should be a way for PQ to convert in natural SQL language, since there is a CAST() function in SQL, no?