Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @Anonymous ,
Table.Buffer puts an entire table into memory and prevents change during evaluation, whereas List.Buffer provides a stable list, meaning it has some form of count or order.
Please refer to the following blogs:
Buffer() M Function in Query Editor (Power BI) - Adatis.
Solved: How to Improve Query Reference performance for lar... - Microsoft Power BI Community
Improving Power Query Calculation Performance With List.Buffer()
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.