Forum Discussion
Incremental Refresh and a Merged Query
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.
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
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.
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.
So the RangeStart and RangeEnd parameters don't filter your FY18 table as well? only your FY19 table?
Thanks,
Anthony
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
