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

[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