Append tables explodes file size

Thanks Ritaf1983 

Here is  the code for the extra bits I'm doing to the 5th table that aren't in what I did for the other 4:

#"Filtered Rows" = Table.SelectRows(#"Expanded SSLookup", each ([ASSESSMENT_MAIN] = "YES") and ([ASSESSMENT_SPLIT_SUBJECT] = "MATH" or [ASSESSMENT_SPLIT_SUBJECT] = "READ" or [ASSESSMENT_SPLIT_SUBJECT] = "WRIT")),

    #"Replaced Value7" = Table.ReplaceValue(#"Filtered Rows","READ","COMB",Replacer.ReplaceText,{"ASSESSMENT_SPLIT_SUBJECT"}),

    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","WRIT","COMB",Replacer.ReplaceText,{"ASSESSMENT_SPLIT_SUBJECT"}),

    #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","MATH","COMB",Replacer.ReplaceText,{"ASSESSMENT_SPLIT_SUBJECT"}),

    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value9",{"STAFF_UNIQUE_ID", "ASSESSMENT_CODE_ASSESSMENTNAME", "GPS_MAX", "GRADE_NAME", "NON_SUBMISSION_REASON", "STAFF_ID", "ASSESSMENT_SPLIT_ASSESSTYPE", "ASSESSMENT_SPLIT_BOUNDARYLOW", "ASSESSMENT_SPLIT_BOUNDARYHIGH", "ASSESSMENT_GRADE_FINAL", "ASSESSMENT_MAIN", "SUBTYPESCORE", "ASSESSMENT_SS", "PERIOD_START", "PERIOD_END", "GRADE_SET_CODE", "GRADE_POINTS", "GPS_MIN", "ASSESSMENT_CODE_TESTNAME", "SUMMATIVE_ASSESSMENT_MARK_ID", "SUMMATIVE_ASSESSMENT_MARK_UNIQUE_ID", "ASSESSMENT_ID", "ASSESSMENT_UNIQUE_ID", "SUBJECT_CODE", "SUBJECT_NAME", "ASSESSMENT_CODE", "ASSESSMENT_NAME", "ASSESSMENT_DATE"}),

    #"Removed Duplicates" = Table.Distinct(#"Removed Columns1"),

    #"Added Custom2" = Table.AddColumn(#"Removed Duplicates", "COMB_READ_LOOKUP", each [STUDENT_UNIQUE_ID]&[ASSESSMENT_SPLIT_YEAR]&[ASSESSMENT_SPLIT_DC]&"READYES"),

    #"Added Custom4" = Table.AddColumn(#"Added Custom2", "COMB_WRIT_LOOKUP", each [STUDENT_UNIQUE_ID]&[ASSESSMENT_SPLIT_YEAR]&[ASSESSMENT_SPLIT_DC]&"WRITYES"),

    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "COMB_MATH_LOOKUP", each [STUDENT_UNIQUE_ID]&[ASSESSMENT_SPLIT_YEAR]&[ASSESSMENT_SPLIT_DC]&"MATHYES"),

    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "READ_GRADE", each try SummativeAssessmentWIP2[ASSESSMENT_GRADE_FINAL]{List.PositionOf(SummativeAssessmentWIP2[COMB_LOOKUP],[COMB_READ_LOOKUP],0)} otherwise null),

    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "WRIT_GRADE", each try SummativeAssessmentWIP2[ASSESSMENT_GRADE_FINAL]{List.PositionOf(SummativeAssessmentWIP2[COMB_LOOKUP],[COMB_WRIT_LOOKUP],0)} otherwise null),

    #"Added Custom8" = Table.AddColumn(#"Added Custom7", "MATH_GRADE", each try SummativeAssessmentWIP2[ASSESSMENT_GRADE_FINAL]{List.PositionOf(SummativeAssessmentWIP2[COMB_LOOKUP],[COMB_MATH_LOOKUP],0)} otherwise null),

    #"Added Custom9" = Table.AddColumn(#"Added Custom8", "ASSESSMENT_SPLIT_ASSESSTYPE", each "C"),

    #"Added Custom10" = Table.AddColumn(#"Added Custom9", "ASSESSMENT_GRADE_FINAL", each if [READ_GRADE] = null or [WRIT_GRADE] = null or [MATH_GRADE] = null then null else if [READ_GRADE] = "WB" or [WRIT_GRADE] = "WB" or [MATH_GRADE] = "WB" then "WB" else if [READ_GRADE] = "WT" or [WRIT_GRADE] = "WT" or [MATH_GRADE] = "WT" then "WT" else if [READ_GRADE] = "EXP" or [WRIT_GRADE] = "EXP" or [MATH_GRADE] = "EXP" then "EXP" else "GD"),

    #"Added Custom11" = Table.AddColumn(#"Added Custom10", "COMB_COMBINATION_EXP", each (if [READ_GRADE] = "EXP" or [READ_GRADE] = "GD" then "R" else "") & (if [WRIT_GRADE] = "EXP" or [WRIT_GRADE] = "GD" then "W" else "") & (if [MATH_GRADE] = "EXP" or [MATH_GRADE] = "GD" then "M" else "")),

    #"Added Custom12" = Table.AddColumn(#"Added Custom11", "COMB_COMBINATION_GD", each (if [READ_GRADE] = "GD" then "R" else "") & (if [WRIT_GRADE] = "GD" then "W" else "") & (if [MATH_GRADE] = "GD" then "M" else "")),

    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom12",{"COMB_READ_LOOKUP", "COMB_WRIT_LOOKUP", "COMB_MATH_LOOKUP", "READ_GRADE", "WRIT_GRADE", "MATH_GRADE"})

The most demanding things I can see in that list would be a dedupe (down from 4200ish rows to 900 rows) and a few lookups using List.PositionOf - everything else is standard if then within the table with very basic conditions and results.

The table that comes out the other side also makes sense in terms of row count (about 20,000 rows) and looking through it I can seee anything there that would be causing it to balloon to 9.2GB (that's where it stopped in the end last night). I have used a merge tables in the earlier 4 into 1 consolidation but that's a single column being brought in, and is only showing as 10MB and completes quickly in the other mid-way calculation table.

Edit - have done a full PC restart - the pbix file sizes for the model before and after these additions has barely changed (189MB to 190MB), the main table I'm appending onto is refreshing as just under 10MB with 19,500 rows and the COMB table (the one with the code above) and the final master table (the one with these two tables appended together and no other code) are 9.2GB and 900 / 20,400 rows respectively. Just in case that helps narrow it down at all...