Forum Discussion
Incremental Refresh - Not retaining old records
I have a SQL query that pulls data from a table that has 30 days of records. I want to set up Incremental Refresh to pull the new records and keep the old records for up to a year.
From what I read, this can be done but when I set it up, it always deleted the old records when incrementally refreshing the new data.
I have set up the parameters below on the table's date column. I have also tried setting the Range Start to be yesterday but then the widgets only show yesterday.
Here is my incremental refresh setup on the table.
Every time the data refreshes, it drops the old records that are no longer on the SQL data and adds the new ones. Does anyone have an idea how to get this to work so it keeps the old records for a year?
I had to convert the date field to a date/time. It now looks like this "06/16/22 12:00:00 AM". I changed the parameters to match the date/time format and now it lets me set up the incremental update without the folding error. I will be testing it this evening when the new data comes in and will report back.
11 Replies
Just to follow up... Converting the SQL SELECT to a VIEW and then formatting the date column to a date/time ("06/16/22 12:00:00 AM"), solved the issue. Converting to a VIEW fixed the folding error. However, it still wouldn't let me set it up until I change the date to date/time. It would have been helpful if that requirement was in the M$ documentation.
Thanks for the update!
Can you provide the M syntax in Advanced editor for your query?
I have two tables. This is the table that pulls the data from SQL. I have a second table that duplicates this one and summarizes it.
let Source = Sql.Database("ITPRODSQL1", "AWCMDB", [Query="SELECT ScheduledShip = CONVERT ( DATE, oh.ScheduledShip, 1 )#(lf) , oh.ControlID#(lf) , oh.ShipFrom#(lf) , pvt.VendorItemNumber#(lf) , pvt.VendorDescription#(lf) , QTYOrdered = COALESCE ( SUM ( od.QtyOrdered ), 0 )#(lf) , QTYShipped = COALESCE ( SUM ( od.QtyShipped1 ), 0 )#(lf)#(tab)#(tab) , oh.ReplacementOrder#(lf)FROM AWCMDB..OrderHeader oh (NOLOCK)#(lf) JOIN AWCMDB..OrderDetail od (NOLOCK)#(lf) ON od.ControlID = oh.ControlID#(lf) JOIN ProductMFG..ProductVendorTranslations pvt (NOLOCK)#(lf) ON pvt.StyleCode = od.Style#(lf) AND pvt.ModelID = od.ModelID#(lf) AND pvt.SKUType <> 'NOTRSI'#(lf)WHERE oh.ScheduledShip < CONVERT ( DATE, GETDATE ( ), 1 )#(lf) AND oh.ShipFrom IN (#(lf) 'R1'#(lf) , 'R2'#(lf) , 'R3'#(lf) )#(lf) AND oh.OrderStatus = 600#(lf)GROUP BY CONVERT ( DATE, oh.ScheduledShip, 1 )#(lf) , oh.ControlID#(lf) , oh.ShipFrom#(lf) , pvt.VendorItemNumber#(lf) , pvt.VendorDescription#(lf)#(tab)#(tab) , oh.ReplacementOrder#(lf) , DATEDIFF ( DAY, GETDATE ( ), oh.ScheduledShip )#(lf)ORDER BY CONVERT ( DATE, oh.ScheduledShip, 1 ) DESC#(lf) , oh.ShipFrom#(lf) , oh.ControlID", CommandTimeout=#duration(0, 0, 5, 0)]), #"Filtered Rows" = Table.SelectRows(Source, each [ScheduledShip] <= RangeEnd and [ScheduledShip] >= RangeStart) in #"Filtered Rows"Which query is not refreshing correctly? Both?
Also can you try removing any date filter in your sql query? as the date filter is going to be driven by the parameters RangeStart and RangeEnd.
I also wonder if your complete query (M script) is being folded completely).
I have adjusted the SQL as below. I split the detail and summary queries, so they pull their data separately, just in case referencing the detail query was causing the problem. Neither query is working but on the server side, it does say it is doing incremental refresh, even though it still looks like it is doing a full refresh (deleting old records).
let Source = Sql.Database("ITPRODSQL1", "AWCMDB", [Query="SELECT ScheduledShip = CONVERT ( DATE, oh.ScheduledShip, 1 ) , oh.ControlID , oh.ShipFrom , VendorItemNumber = COALESCE ( pvt.VendorItemNumber, CAST(od.ModelID AS CHAR (25))) , VendorDescription = COALESCE ( pvt.VendorDescription, od.Description ) , QTYOrdered = COALESCE ( SUM ( od.QtyOrdered ), 0 ) , QTYShipped = COALESCE ( SUM ( od.QtyShipped1 ), 0 ) , oh.ReplacementOrder FROM AWCMDB..OrderDetail od (NOLOCK) JOIN AWCMDB..OrderHeader oh (NOLOCK) ON od.ControlID = oh.ControlID AND oh.ShipFrom IN ( 'R1' , 'R2' , 'R3' ) AND oh.OrderStatus = 600 LEFT JOIN ProductMFG..ProductVendorTranslations pvt (NOLOCK) ON pvt.StyleCode = od.Style AND pvt.ModelID = od.ModelID AND pvt.SKUType <> 'NOTRSI' GROUP BY CONVERT ( DATE, oh.ScheduledShip, 1 ) , oh.ControlID , oh.ShipFrom , COALESCE ( pvt.VendorItemNumber, CAST(od.ModelID AS CHAR (25))) , COALESCE ( pvt.VendorDescription, od.Description ) , oh.ReplacementOrder , DATEDIFF ( DAY, GETDATE ( ), oh.ScheduledShip ) ORDER BY CONVERT ( DATE, oh.ScheduledShip, 1 ) DESC , oh.ShipFrom , oh.ControlID", CommandTimeout=#duration(0, 0, 5, 0)]), #"Filtered Rows" = Table.SelectRows(Source, each [ScheduledShip] <= RangeEnd and [ScheduledShip] >= RangeStart) in #"Filtered Rows"I also adjusted the refresh as shown below. There is a message that it is unable to confirm if M-query can be folded and I have found no way to fix it.
I think that is it. The query cant be folded completely. My guess is because you added a SQL query and then set the filter using M (power query), that broke the folding.
One thing you can try is to create a view using your SQL query and store in your database, then connect to that query in power bi and add the rangestart and rangeend. And it should look similar to below where you dont have your sql query.let Source = Thatviewthatyoucreate, #"Filtered Rows" = Table.SelectRows(Source, each [ScheduledShip] <= RangeEnd and [ScheduledShip] >= RangeStart) in #"Filtered Rows"I created a view and added the filter.
let Source = Sql.Database("devtest\ittestsql1", "awcmdb"), dbo_MTSBackorders_VW1 = Source{[Schema="dbo",Item="MTSBackorders_VW1"]}[Data], #"Filtered Rows" = Table.SelectRows(dbo_MTSBackorders_VW1, each [ScheduledShip] <= RangeEnd and [ScheduledShip] >= RangeStart) in #"Filtered Rows"It no longer shows the folding message but now it doesn't see the filter set up with the parameters.
