Forum Discussion
Solved
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 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.
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).