Results 1 to 1 of 1

Thread: Query is reading the same table twice

  1. #1
    Neophyte parkourtofu's Avatar
    Join Date
    Sep 2020
    Posts
    1
    Articles
    0
    Excel Version
    Excel Office 365

    Question Query is reading the same table twice



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •