Incremental Refresh and a Merged Query

[dgwilson]

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

[v-juanli-msft]

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]

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]

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.

[dgwilson · Accepted solution]

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.

[anthonyloh]

So the RangeStart and RangeEnd parameters don't filter your FY18 table as well? only your FY19 table?

Thanks,

Anthony

[dgwilson]

Hi @anthonyloh 

I have this working now. Both tables have rangestart and rangeend filtering on them.

What I discovered (once the error messaging improved) was that the FY18 table did NOT have a LAST_MODIFIED (date/time) column and that when this was merged with the main table it came in with null values. When the incremental refresh ran ... which checks LAST_MODIFIED ... things kind of self destructed.

I've highlighted in BOLD the key rows in both queries.

Here's the M code for the FY18

let
Source = Oracle.Database("revenp", [HierarchicalNavigation=true]),
REVENUE = Source{[Schema="REVENUE"]}[Data],
EVO_DMT_BUS_CUST_PROFIT_FY1 = REVENUE{[Name="EVO_DMT_BUS_CUST_PROFIT_FY18"]}[Data],
#"Filtered Rows" = Table.SelectRows(EVO_DMT_BUS_CUST_PROFIT_FY1, each [RPT_MONTH] >= RangeStart and [RPT_MONTH] < RangeEnd),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "LAST_MODIFIED", each DateTime.Date(#datetime(2018, 07, 01, 00, 00, 00))),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"LAST_MODIFIED", type datetime}})
in
#"Changed Type"

here's the M code for the main table ... including the merge

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],
#"Removed Other Columns" = Table.SelectColumns(EVO_DMT_BUS_CUST_PROFIT_VW1,{"DATA_SOURCE", "RPT_MONTH", "ENTRY_TEXT", "PL_CATEGORY", "AMOUNT_GC", "HL_PROD_CLASS", "DEEMED_COST_SOURCE", "DEEMED_COST_PERC", "DEEMED_COST_AMOUNT", "AMOUNT_GC_DISP_REV", "AMOUNT_GC_DISP_COST", "AMOUNT_GC_ACTUAL_COST", "SAP_ACCOUNT", "SAP_ACCOUNT_DESC", "SAP_PRODUCT", "SAP_PRODUCT_DESC", "PROFIT_CENTRE", "PROFIT_CENTRE_DESC", "QTY", "SAP_MATERIAL_CODE", "PARTNER_PROFIT_CENTRE", "WBSE", "WBSE_DESC", "PROJECT_CODE", "PROJECT_CODE_DESC", "CONTRACT_TYPE", "SALES_CHANNEL", "PORTFOLIO", "UNIT_OF_MEASURE", "DOC_TYPE", "ACTIVITY", "REFDOC_NO", "REFDOC_DESC", "SALES_ORDER", "PURCHASE_DOC", "VENDOR_CODE", "NETWORK_ID", "FIN_YEAR", "FIN_PERIOD", "POSTING_DATE", "DOC_DATE", "ENTRY_DATE", "USER_NAME", "USER_NAME_DESC", "PARKED_BY", "PARKED_BY_DESC", "DATA_TYPE_SUMMARY", "DATA_TYPE_DETAIL", "TRAN_TYPE", "TRAN_CODE", "SPOT_CODE", "SPOT_CODE_DESC", "CATS_EMPLOYEE_NO", "CATS_EMPLOYEE_NAME", "LAST_MODIFIED", "EXCLUSION_FLAG", "EXCLUSION_REASON", "HEAD_PARTY_NO_CURR", "HEAD_PARTY_NAME_CURR", "PC_LEVEL6_CURR", "PROJECT_CODE_DESC_CURR", "HL_PROD_CLASS_CURR"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"HEAD_PARTY_NO_CURR", "HEAD_PARTY_NO"}, {"HEAD_PARTY_NAME_CURR", "HEAD_PARTY_NAME"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [RPT_MONTH] >= RangeStart and [RPT_MONTH] < RangeEnd),
#"Trimmed Text" = Table.TransformColumns(#"Filtered Rows",{{"HEAD_PARTY_NO", Text.Trim, type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Trimmed Text",{{"HEAD_PARTY_NO", "HEAD_PARTY_NO_input"}, {"HEAD_PARTY_NAME", "HEAD_PARTY_NAME_input"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns1", "HEAD_PARTY_NO", each if [HEAD_PARTY_NO_input] = "N/ALEASING" then "" else if [HEAD_PARTY_NO_input] = "TBA" then "" else [HEAD_PARTY_NO_input]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"HEAD_PARTY_NO", Int64.Type}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type2", "HEAD_PARTY_NAME", each if [HEAD_PARTY_NO_input] = "N/ALEASING" then [HEAD_PARTY_NO_input] else if [HEAD_PARTY_NO_input] = "TBA" then [HEAD_PARTY_NO_input] else [HEAD_PARTY_NAME_input]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"PROFIT_CENTRE", type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"QTY", type number}}),
#"Appended Query" = Table.Combine({#"Changed Type1", EVO_DMT_BUS_CUST_PROFIT_FY18})
in
#"Appended Query"

It's all been running like a dream for months now.

- David