Hello!
I retrieved all E-Mails via Excel Enterprise and a Connection to the Exchange Server.
let
Source = Exchange.Contents("companyemail"),
Mail1 = Source{[Name="Mail"]}[Data],
#"Expanded Sender" = Table.ExpandRecordColumn(Mail1, "Sender", {"Address"}, {"Sender.Address"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Sender", "Sender.Address", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Sender.Address.1", "Sender.Address.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sender.Address.1", type text}, {"Sender.Address.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sender.Address.1", "DisplayTo", "DisplayCc", "ToRecipients", "CcRecipients", "BccRecipients", "DateTimeSent", "Importance", "Categories", "IsRead", "HasAttachments", "Attachments", "Preview", "Attributes", "Id"}),
#"Expanded Body" = Table.ExpandRecordColumn(#"Removed Columns", "Body", {"TextBody"}, {"Body.TextBody"}),
Afterwards I am filtering all the E-Mails as only specific domains are interesting to me (about 250 domains). For that I have a List with all domains I want to look into.
#"Filtered Rows" = Table.SelectRows(#"Expanded Body", each ((List.Contains(ListOfDomains, [Sender.Address.2]) = true))),
In the last step I need to remove all names of our employees within the column Body.TextBody. I have a List with all employeenames . As there are more than 50k of Emails it takes quite some time to do the last step. I need to remove all names within the Body.TextBody Column for privacy reasons. I read about list.buffer and table.buffer but I don't understand how to properly make use of them. At the moment I think the list "Mitarbeiternamen" is loaded everytime into my system (not sure but it feels like it). If i'd buffer the list I think my query would run much faster as right now it takes about 2 hours. Can anybody help me?
Custom1 = List.Accumulate(Mitarbeiternamen, #"Filtered Rows", (table, old) => Table.ReplaceValue(table, old, "", Replacer.ReplaceText, {"Body.TextBody"}))
in
Custom1
