Power Query RangeStart and RangeEnd Date Conversion error while using SQL

[itsmebvk]

Hi Folks,

I am trying to setup incremental refresh in Power BI Dataset using SQL. I am using SQL server as source and I am using SQL in Power Query (Created RangeStart and RangeEnd parameter in power query first)

Query :

Sql.Database("Server", "DBName", [Query="

SELECT
DISTINCT(Order_Date) Posting_Date
FROM Sales.OrdersTable
where
Order_Date >= '" & Text.From(RangeStart) & "' and Order_Date < '" & Text.From(RangeEnd) & "'
"])

When I use above expression in Power Query it is working fine and loaded data based on my Rangestart and Rangeend paramters. But when I refreshd this dataset in power bi service ,I am getting following error (replaced DB and server name below). I have created lot of datasets successfully using incremental refresh earlier but this is the first time I am using SQL. Can some one please suggest me how to fix this issue?

	
	Data source error:	DataSource.Error: <pii>Microsoft SQL: Conversion failed when converting date and/or time from character string.</pii>. DataSourceKind = <pii>SQL</pii>. DataSourcePath = <pii>ServerName.database.windows.net;DatabaseName1</pii>. Message = <pii>Conversion failed when converting date and/or time from character string.</pii>. ErrorCode = <pii>-2146232060</pii>. Number = <pii>241</pii>. Class = <pii>16</pii>. . The exception was raised by the IDbCommand interface.
Cluster URI:	WABI-SOUTH-XXXX-redirect.analysis.windows.net
Activity ID:	b579XXXXXXXXXXXXXXXX
Request ID:	e6feXXXXXXXXXXXXXXXXXXXX
Time:	2021-12-04 12:54:07Z

Thanks in advance.

Regards

BVK

[Madalina2801]

Hi,

I know this is an old thread but I'm having a similar issue. I was trying to use a native query with RangeStart and RangeEnd but the incremental refresh is not working in Service, no new data is added to the table, it just performs the historical load but then the new partitions are empty. Here is my code.

let
Source = Sql.Database(Server, Database),
SqlQuery = "
SELECT *,operationName + ' (' + FORMAT(durationMin, '0.0') + ' min)' AS NewColumn FROM [Monitoring].[dbo].[my_table]
WHERE [time_timestamp] >= '" & DateTime.ToText(RangeStart, "yyyy-MM-dd hh:mm:ss") & "'
AND [time_timestamp] < '" & DateTime.ToText(RangeEnd, "yyyy-MM-dd hh:mm:ss") & "'",
Result = Value.NativeQuery(
Source,
SqlQuery,
null,
[EnableFolding = true]
),
TypedResult = Table.TransformColumns(Result, {
{"durationMin", Number.From, Currency.Type}
})
in
TypedResult

I can see the "View Native Query" option enable but I'm not sure what I'm doing wrong. 

Any help would be appreciated.

Thank you!

[jeroendekk]

Hi @itsmebvk 
I feel the SQL query should include a "COVERT(datetime2," function. But I cannot get it to work either. However I think I do have solution.

If you use the Value.NativeQuery function you can actually combine a Native SQL query with Power Query steps amd keep Query folding.

There is a video about that here: 
Enable QUERY FOLDING for native queries in Power Query / Power BI - YouTube

You could do the datefilters in Power Query outside of your SQL.
I have tried with and AdventureWorks database on Azure and it works for me.

let
    Source = Sql.Database("oefen.database.windows.net", "AdventureWorksDW"),
    NativeQuery = Value.NativeQuery(Source, "SELECT 
      [SalesOrderNumber]
      ,[OrderQuantity]
      ,[UnitPrice]
      ,[ExtendedAmount]
      ,[OrderDate] FROM [dbo].[FactResellerSales]",null, [EnableFolding=true]),
    #"Filtered Rows" = Table.SelectRows(NativeQuery, each [OrderDate] > RangeStart),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
    #"Filtered Rows1"

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Best regards,
Jeroen

[itsmebvk · Accepted solution]

@jeroendekk  Thanks for your inputs.  Above solution may not work exactly in my case, as I have four union queries where I have to pass my Date filters to each union query before execution. But the solution you have provided gave me a light.

Finally I was able to identify the issue. The problem is with format of Date (YYYY-MM-DD 12:00:00 AM) and RangeStart parameter (MM/DD/YYYY 12:00:00 AM) both formats are  different . I have to fix my code little bit then it worked.

Sql.Database(Server, Database, [Query="  
		        SELECT
				DISTINCT(Order_Date) Posting_Date
				FROM Order_Table
							where
						    Order_Date >= '" & DateTime.ToText(RangeStart, "yyyy-MM-dd hh:mm:ss") & "' and Order_Date < '" & DateTime.ToText(RangeEnd, "yyyy-MM-dd hh:mm:ss") & "'"])

Thank you so much again

[jeroendekk]

Hi @itsmebvk 
You are using the Text.From function. This means the parameter that sets the filter is a text version of a datetime. Is this correct. This seems unusual for a sql source, and the error message seems to indicate it requires a date/time for the partitioning.

Can you check which datatype the datecolumn is in the SQL source?

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Best regards,
Jeroen

[itsmebvk]

@jeroendekk  Thanks for your reply. You are correct, In source my column is Datetime Datatype. Inorder to use this in Mquery I am just changing this to text. This solution working in Desktop and filtering based on my RangeStart and RangeEnd  parameter. I am only facing this issue in service.

I am also referring this video 22:00 Minutes.

https://www.youtube.com/watch?v=KEh2Udm6ibA