Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
itsmebvk
Continued Contributor
Continued Contributor

Power Query RangeStart and RangeEnd Date Conversion error while using SQL

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

1 ACCEPTED SOLUTION
itsmebvk
Continued Contributor
Continued Contributor

@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

View solution in original post

5 REPLIES 5
Madalina2801
Advocate II
Advocate II

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
Resolver V
Resolver V

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
Continued Contributor
Continued Contributor

@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
Resolver V
Resolver V

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
Continued Contributor
Continued Contributor

@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

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.