Forum Discussion

cward's avatar
cward
Regular Visitor
4 years ago
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...
Need Help
  • cward's avatar
    cward
    4 years ago

    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.  

Tutu_in_YYC's avatar
Tutu_in_YYC
Icon for Super User rankSuper User
4 years ago

Can you provide the M syntax in Advanced editor for your query?

  • cward's avatar
    cward
    Regular Visitor
    4 years ago

    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"

     

    • Tutu_in_YYC's avatar
      Tutu_in_YYC
      Icon for Super User rankSuper User
      4 years ago

      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).