cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lehas
New Member

Increasing performance with list.buffer or table.buffer (removing names from list in another column)

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

 

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @Lehas ,

 

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Kudoed Authors