Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
@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
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |