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.  

cward's avatar
cward
Regular Visitor
4 years ago

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.

 

 

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Super User
    4 years ago

    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"

     

    • cward's avatar
      cward
      Regular Visitor
      4 years ago

      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.

       

      • Tutu_in_YYC's avatar
        Tutu_in_YYC
        Super User
        4 years ago

        But the parameters are set up right? With correct data type?