It is not to much but your formula for the condition column is not efficient as it going to apply a filter over all 70K rows, use the next formula, in this case, the result of the previous steps are summarized by group by (changed the row number from 70 k to lower rows), then this table is used for calculation of condition
let
Source = Excel.Workbook(File.Contents("C:\Users\n11472235\OneDrive - Queensland University of Technology\Desktop\qook2.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Scripts", type text}, {"Vendor", Int64.Type}, {"VendorName", type text}, {"BusinessArea", type any}, {"CompanyCode", type text}, {"FiscalYear", Int64.Type}, {"DocumentType", type text}, {"DocumentDate", type date}, {"PostingKey", Int64.Type}, {"PostingDate", type date}, {"DocumentNumber", Int64.Type}, {"Reference", type text}, {"Amountindoccurr", type number}, {"DocumentCurrency", type text}, {"Amountinlocalcurr", type number}, {"LocalCurrency", type text}, {"ClearingDocument", Int64.Type}, {"ClearingDate", type text}, {"Text", type text}, {"NetDueDate", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"DocumentType", "Reference", "Amountindoccurr", "Text"}),
#"Added Custom"= Table.AddColumn(#"Removed Other Columns", "NewColumn",
each if [DocumentType] = "ZA" and [Text] <> "" then
Text.Combine({[DocumentType], [Text], Number.ToText(Number.Abs([Amountindoccurr]))})
else if [DocumentType] = "RE" and [Reference] <> "" then
Text.Combine({[DocumentType], [Reference], Number.ToText(Number.Abs([Amountindoccurr]))})
else
""
),
#"Grouped Rows" = Table.Group(#"Added Custom", {"NewColumn"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Add Custom1" = Table.AddColumn(#"Added Custom", "Condition", each try #"Grouped Rows"{[NewColumn=(if Text.StartsWith([NewColumn], "RE") then Text.Replace([NewColumn], "RE", "ZA") else if Text.StartsWith([NewColumn], "ZA") then Text.Replace([NewColumn], "ZA", "RE") else null)]}[Count] otherwise null)
in
#"Add Custom1"
