Forum Discussion

dgwilson's avatar
dgwilson
Resolver III
7 years ago
Solved

Incremental Refresh and a Merged Query

I have Financial Year 2019 data (currently at 19 million rows of data and growing). The idea is to refresh this data via Incremental refresh (and as far as I can tell that's working) out of the oracle database.

 

I have Financial Year 2018 data (that is 55,000 rows of data). This data has come from a different source and exists in a separate table out of the oracle database. This data will never change.

So in PowerQuery the main table (with the incremental refresh), I've appended the FY18 data.

 

The issue I have is that after a single refresh in the cloud, the FY19 data looks great. The FY18 data has been multiplies by about 56 times... the number of rows of data has grown significantly. Clearly not what I want.

 

Can I have an incremental refresh table merged with a static table? 

All of my measures and additional columns need to operate over the single combined table.

 

- David

Need Help
  • dgwilson's avatar
    dgwilson
    7 years ago

    This issue may be solved. To be confirmed.

     

    I have added the RangeStart and RangeEnd parameters to te FY18 table - so they are across two tables now.

    And I have right clicked on the FY18 table and also added the incremental refresh specification to that table as well - previously it was only on the FY19+ table.

     

    Initial refresh test has been successful.

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi dgwilson 

    I can't reproduce your problem, could you show more details so that i can reproduce this problem?

     

    Please check this article, let me know how you configure the parameter “RangeStart” and  “RangeEnd”,

    also rows storage policy and rows refresh policy in the Incremental Refresh window.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • dgwilson's avatar
      dgwilson
      Resolver III
      7 years ago

      Here is the M code (it's easier to show here than a series of screen shots).

       

      This takes the FY19 table - EVO_DMT_BUS_CUST_PROFIT_VW

      Filters it with the definitions of RangeStart and RangeEnd

      Removes unwanted columns

      I've checked at this point that the Native Query is still OK (i.e. right click on Removed Columns : View Native Query).

      You can see in the M code where the FY18 Data is appended.

       

       

      let
    Source = Oracle.Database("revenp", [HierarchicalNavigation=true]),
    REVENUE = Source{[Schema="REVENUE"]}[Data],
    EVO_DMT_BUS_CUST_PROFIT_VW1 = REVENUE{[Name="EVO_DMT_BUS_CUST_PROFIT_VW"]}[Data],
    #"Filtered Rows" = Table.SelectRows(EVO_DMT_BUS_CUST_PROFIT_VW1, each [RPT_MONTH] >= RangeStart and [RPT_MONTH] < RangeEnd),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"CUST_PROFIT_ID", "BUS_TRANS_ID", "DEEMED_COST_PERC", "CO_CODE", "SALES_CHANNEL", "PORTFOLIO", "DOC_TYPE", "REFDOC_NO", "REFDOC_DESC", "DOCUMENTNO", "SAP_RECORD_NO", "SALES_ORDER", "PURCHASE_DOC", "VENDOR_CODE", "NETWORK_ID", "AMOUNT_TC", "CURR_TC", "PC_LEVEL1", "PC_LEVEL2", "PC_LEVEL3", "PC_LEVEL4", "PC_LEVEL5", "PC_LEVEL6", "GL_LEVEL8", "GL_LEVEL9", "GL_LEVEL10", "GL_LEVEL11", "PR_LEVEL1", "PR_LEVEL2", "PR_LEVEL3", "PR_LEVEL4", "PR_LEVEL5", "PR_LEVEL6", "DATA_TYPE_SUMMARY", "DATA_TYPE_DETAIL", "ICMS_GL", "ICMS_SUBTYPE", "TRAN_TYPE", "TRAN_CODE", "CALL_TYPE", "CALL_TYPE_DESC", "CHARGE_CODE", "ICMS_ACCOUNT_NO", "ICMS_CUSTOMER_NO", "ICMS_CUSTOMER_NAME", "CHARGE_AMT", "DISCOUNT_AMT_CP", "DISCOUNT_AMT_ABCALL", "DISCOUNT_AMT_ABOTHER", "DISCOUNT_AMT_PLUG", "BILL_CYCLE", "ADJ_DESC", "ADJ_BATCH_ID", "ADJ_REF_NO", "ICMS_INPUT_HASH", "MANUAL_INSERT_ID", "PR_LEVEL1_T2PH", "PR_LEVEL2_T2PH", "HEAD_PARTY_NO_CURR", "HEAD_PARTY_NAME_CURR", "PC_LEVEL1_CURR", "PC_LEVEL2_CURR", "PC_LEVEL3_CURR", "PC_LEVEL4_CURR", "PC_LEVEL5_CURR", "PC_LEVEL6_CURR", "GL_LEVEL8_CURR", "GL_LEVEL9_CURR", "GL_LEVEL10_CURR", "GL_LEVEL11_CURR", "PR_LEVEL1_CURR", "PR_LEVEL2_CURR", "PR_LEVEL3_CURR", "PR_LEVEL4_CURR", "PR_LEVEL5_CURR", "PR_LEVEL6_CURR"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"HEAD_PARTY_NO", type number}}),
    #"Appended Query" = Table.Combine({#"Changed Type", EVO_DMT_BUS_CUST_PROFIT_FY18})
in
    #"Appended Query"

       

       

      Below is the RangeEnd definition - Range Start is the same (except the date is one day earlier)

       

      And here's the Incremental refresh definition for the table.

       

      Here's my Row counts BEFORE cloud upload and refresh.

       

      The after I'll do in a separate post tomorrow morning.

       

      - David

       

      • dgwilson's avatar
        dgwilson
        Resolver III
        7 years ago

        And after the incremental refresh has run... this is what I get...

        I've highlighted in red the FY18 data that has changed - that should not have changed.

         

        By the way, the Financial year is 1 July to 30 June.

         

        Below is an annotated image to better explain what I'm talking about.