Incremental Refresh and a Merged Query

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