Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
dgwilson
Resolver III
Resolver III

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.

2019-05-28 07_10_03-.png

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

1 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.

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

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.

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)

2019-05-29 21_33_06-RangeEnd-Parameters.png

 

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

2019-05-29 22_33_51-IncreRefresh-CUST-PROFIT_VW.png

 

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

2019-05-29 22_36_08-Before-Cloud-Refresh.png

 

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.

 

2019-05-29 22_36_08-After-Cloud-Refresh.png

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

2019-05-30 08_11_20-2019-05-29 22_36_08-Cloud-Refresh-Summary-annotated.png

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors