parkourtofu
New member
- Joined
- Sep 29, 2020
- Messages
- 1
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Excel Office 365
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
fnGetTotPrem<- it's a function i sumifs the premiums by "FILE"(which file were the policy number from), "CHDRNUM"(policy number) and "RECORD"(count)
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,
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":
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
fnGetTotPrem<- it's a function i sumifs the premiums by "FILE"(which file were the policy number from), "CHDRNUM"(policy number) and "RECORD"(count)
Code:
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,
Last edited by a moderator:
