Dates loading into Power Query

[turp111]

Current situation when getting data from data source (IBM DB2 Database):

 - If I connect and do a native query, dates comes over in date format

 - If I connect and submit a SQL query instead of a native, the dates come over in datetime format (quite odd)

My dilemma is that I'm working on implementing incremental refreshes, which require datetime formats for the columns I'm using.  And to do that I need the queries to fold.  And to do that I can't use a SQL query.  I need datetime formats with native queries.

I can't do a simple type change because that will also stop the query fold right where I need it.

What I need is to be able convert the dates to datetime at some higher level (like when it loads into Power Query), or somehow be able to change the type to datetime and not break query folding.

I've also tried the method here to convert the RangeStart/RangeEnd parameters to date but it didn't work and I think there is a typo in the syntax.

This is why I have a severe love/hate relationship with Power BI.

Thanks...plz help.

[Anonymous]

According to the documentation, you can use a Native Query (which I guess is not the same as a "SQL query"). 

"There's no requirement the final query support folding. For example in the following expression, we use a non-folding NativeQuery but integrate the RangeStart and RangeEnd parameters directly into SQL:

Power Query M

let
Query = "select * from dbo.FactInternetSales where OrderDateKey >= '"& Text.From(Int32.From( DateTime.ToText(RangeStart,"yyyyMMdd") )) &"' and OrderDateKey < '"& Text.From(Int32.From( DateTime.ToText(RangeEnd,"yyyyMMdd") )) &"' ",
Source = Sql.Database("dwdev02","AdventureWorksDW2017"),
Data = Value.NativeQuery(Source, Query, null, [EnableFolding=false])
in
Data

However, if the incremental refresh policy includes getting real-time data with DirectQuery, non-folding transformations can't be used. If it’s a pure import mode policy without real-time data, the query mashup engine might compensate and apply the filter locally, which requires retrieving all rows for the table from the data source. This can cause incremental refresh to be slow, and the process can run out of resources either in the Power BI service or in an On-premises Data Gateway - effectively defeating the purpose of incremental refresh."

--Nate

[Anonymous]

If you write a SQL query, it doesn't have to fold--being that it's already a SQL query going directly to your database, right? Folding means translating the M code to the source language. You are already doing that.

--Nate

[turp111]

Yes, but the larger issue I'm trying to solve for is being able to do incremental refreshes, which I can't do with a SQL query.  I have some large tables with several million rows and doing inc. refreshes would greatly help.