Query is reading the same table twice

[parkourtofu]

Dear experts,

i would like to seek help in improving the efficiency of my codings below.

my want is this, a consolidated summary in powerpivot that shows number of policies and sum of premiums according to their "FILE" and "ZMOVECODE":

Book5.xlsx (28.2 KB)

GOAL: i have a total of 100GB csv documents as raw datasets and i would like to produce a pivot table like above

at my coding:
i have stage query to import the respective files

• SQ_ActVal_IFBEG
• SQ_ActVal_ET
• SQ_ActVal_EX
• SQ_ActVal_IF

TRAD_LForm_Table <- it's a table i extract a few infos (i.e. LFormDesc,Productname and Fund4) to form the pivot table above
fnGetTotPrem<- it's a function i sumifs the premiums by "FILE"(which file were the policy number from), "CHDRNUM"(policy number) and "RECORD"(count)

let
    sourceIFBEG = SQ_ActVal_IFBEG,
    #"Merged Queries1" = Table.NestedJoin(sourceIFBEG, {"CNTTYPE", "CRTABLE", "Enhanced_Ind"}, TRAD_LForm_Table, {"CNTTYPE", "CRTABLE", "ENHANCED_IND"}, "TRAD_LForm_Table", JoinKind.LeftOuter),
    #"Expanded TRAD_LForm_Table1" = Table.ExpandTableColumn(#"Merged Queries1", "TRAD_LForm_Table", {"LFormDesc", "PRODUCTNAME", "FUND4"}, {"LFormDesc", "PRODUCTNAME", "FUND4"}),
    IFBEG = Table.SelectRows(#"Expanded TRAD_LForm_Table1", each ([LFormDesc] <> null)),
    
    IFBEG_Prem = fnGetTotPrem(IFBEG),


    sourceET = SQ_ActVal_ET,
    #"Merged Queries2" = Table.NestedJoin(sourceET, {"CNTTYPE", "CRTABLE", "Enhanced_Ind"}, TRAD_LForm_Table, {"CNTTYPE", "CRTABLE", "ENHANCED_IND"}, "TRAD_LForm_Table", JoinKind.LeftOuter),
    #"Expanded TRAD_LForm_Table2" = Table.ExpandTableColumn(#"Merged Queries2", "TRAD_LForm_Table", {"LFormDesc", "PRODUCTNAME", "FUND4"}, {"LFormDesc", "PRODUCTNAME", "FUND4"}),
    ET = Table.SelectRows(#"Expanded TRAD_LForm_Table2", each ([LFormDesc] <> null)),


    ET_Prem = fnGetTotPrem(ET),


    sourceEX = SQ_ActVal_EX,
    #"Merged Queries3" = Table.NestedJoin(sourceEX, {"CNTTYPE", "CRTABLE", "Enhanced_Ind"}, TRAD_LForm_Table, {"CNTTYPE", "CRTABLE", "ENHANCED_IND"}, "TRAD_LForm_Table", JoinKind.LeftOuter),
    #"Expanded TRAD_LForm_Table3" = Table.ExpandTableColumn(#"Merged Queries3", "TRAD_LForm_Table", {"LFormDesc", "PRODUCTNAME", "FUND4"}, {"LFormDesc", "PRODUCTNAME", "FUND4"}),
    EX = Table.SelectRows(#"Expanded TRAD_LForm_Table3", each ([LFormDesc] <> null)),


    EX_Prem = fnGetTotPrem(EX),


    sourceIF = SQ_ActVal_IF,
    #"Merged Queries4" = Table.NestedJoin(sourceIF, {"CNTTYPE", "CRTABLE", "Enhanced_Ind"}, TRAD_LForm_Table, {"CNTTYPE", "CRTABLE", "ENHANCED_IND"}, "TRAD_LForm_Table", JoinKind.LeftOuter),
    #"Expanded TRAD_LForm_Table4" = Table.ExpandTableColumn(#"Merged Queries4", "TRAD_LForm_Table", {"LFormDesc", "PRODUCTNAME", "FUND4"}, {"LFormDesc", "PRODUCTNAME", "FUND4"}),
    IF = Table.SelectRows(#"Expanded TRAD_LForm_Table4", each ([LFormDesc] <> null)),
    
    IF_Prem = fnGetTotPrem(IF),


    L678_NOP = Table.Combine({IFBEG,ET,EX,IF}),
    L678_PREM = Table.Combine({IFBEG_Prem,ET_Prem,EX_Prem,IF_Prem}),


    L678 = Table.NestedJoin(L678_NOP,{"FILE","CHDRNUM","RECORD"},L678_PREM,{"FILE","CHDRNUM","RECORD"},"new",JoinKind.LeftOuter),
    #"Expanded new" = Table.ExpandTableColumn(L678, "new", {"Total_Prem"}, {"Total_Prem"})
in
    #"Expanded new"

then here i have. a data ready for powerpivot to do what i wanted. here's the issue that it takes too long to load, and the size im working with now is a fraction of the 100GB that im intended to do for.

please may i seek the expert's view on how can i improve this coding so that it wont read the same table twice so it reduces the time it takes to load at this stage.

Thank you very much,

 

[pinarello]

You did not explain how the 100GB csv documents were composed. If this is, for example, one for each contract, then you could create a pivoted table with the summary values after the first import and remember by means of a query up to which date you have imported the csv documents.

The next time you use this date as a filter to import only newer documents and consolidate them with the last import.

But since you didn't explain how the csv-documents result, I have no idea if my suggestion can be useful.