How to use Table.Buffer to speed queries?


Active Member
Apr 30, 2014
Office Version
  1. 365
  1. Windows
My queries go against transactional tables with 30-40 columns and millions of rows, and I'd like to speed them up. I see columns by Chris Webb and others talking about how List.Buffer and Table.Buffer can greatly reduce query times, but the M function descriptions are pretty sparse on how to use these.

Is there a rule of thumb about when in a query you should operate on a buffered table vs. the original? For instance, it's fairly typical for my queries to do the following:
1) change column types
2) create new columns
3) filter on source and/or new columns
4) merges
not necessarily in that order.

So at what point would I "hand off" to the buffered table, and then return to the source, to optimize a query like the one below?

    SrcFileName = fnGetInternalTableValues("tblQuerySources", 0, 1, "Vouchers"),
    FileSource = fnGetInternalTableValues("tblDefaults", 0, 1, "Data Source Folder Path") & SrcFileName,
    Source = Csv.Document(File.Contents(FileSource),[Delimiter=",",Encoding=1252]),
//    Source = Csv.Document(File.Contents("S:\OFP\SCM Logistics Operations\Sources\3PL_AP_SPEND.csv"),[Delimiter=",",Encoding=1252]),
    PromoteHeaders = Table.PromoteHeaders(Source),
    ChangeColumnTypes = Table.TransformColumnTypes(PromoteHeaders,{
        {"Dept ID", type text}, {"Supplier Name", type text}, {"Origin", type text},
        {"Supplier ID", type text}, {"Line Description", type text}, {"Account Description", type text},
        {"Sum of monetary amount", Currency.Type}, {"Account", type text}, {"Business Unit", type text}, 
        {"PO Number", type text}, {"Line Number", Int64.Type}, {"Schedule Number", Int64.Type}, {"PO Distribution Line Number", Int64.Type}, 
        {"Payment Date", type date}, {"Invoice Date", type date}, {"Payment ID", type text},
        {"Payment Amount", Currency.Type}, {"Merchandise Amt", Currency.Type}, {"Sum Freight", Currency.Type}, 
        {"Unit Price", Currency.Type}, {"Quantity", type number}, {"Payment Method", type text},
        {"Discount Amount", Currency.Type}, {"Discount Due Date", type date}, {"Due Date", type date}, {"Voucher Entered Date", type date},
        {"Matched Date", type date}, {"Payment Terms ID", type text}, {"Payment Terms Description", type text},
        {"Voucher Style", type text}, {"Close Status", type text}, {"Post Status", type text},
        {"Invoice Number", type text}, {"Voucher Source", type text}, {"Match Status", type text},
        {"Voucher ID", type text},{"Bank Code", type text},{"Bank Account", type text}, {"Accounting Date", type date},
        {"Match Line Status", type text}, {"Voucher Line Number", Int64.Type}, {"Voucher Approval Status", type text}, {"Voucher Approval Date", type date}, {"Supplier Persistence", type text}
    RenameColumns = Table.RenameColumns(ChangeColumnTypes,{
        {"Sum of monetary amount", "Voucher Amount"}, {"Matched Date", "Voucher Match Date"},
        {"Sum Freight", "Freight Charges"}, 
        {"Due Date", "Term Due Date"}, {"Origin", "Invoice Source"},
        {"Line Description", "Item Description"}}),
    RemoveDateErrors = Table.RemoveRowsWithErrors(RenameColumns, {"Invoice Date", "Payment Date", 
        "Term Due Date", "Discount Due Date", "Voucher Match Date",
        "Voucher Entered Date", "Accounting Date", "Voucher Approval Date"}),
//    -- For smaller extracts, take only the dates according to the incremental value
//    -- Change the date field below to match the appropriate date field in the source
    DateInc = -30,
    LatestDatesOnly = Table.SelectRows(AddConvFactor, each [Accounting Date] > Date.AddDays(Date.From(DateTime.FixedLocalNow()), DateInc)),
    IsOverride = fnGetInternalTableValues("tblDefaults", 0, 1, "Data History Override"),
    OverrideDuration = fnGetInternalTableValues("tblDefaults", 0, 1, "Days History"),
    IfDateOverride = if IsOverride = "Y"
        then Table.SelectRows(RemoveDateErrors, each Date.IsInPreviousNDays([Invoice Date], OverrideDuration))
        else RemoveDateErrors,
    IsDateFilter = fnGetInternalTableValues("tblDefaults", 0, 1, "Filter Data by Date"),
    DateFilterStart = Date.From(fnGetInternalTableValues("tblDefaults", 0, 1, "Filter Start Date")),
    DateFilterEnd = Date.From(fnGetInternalTableValues("tblDefaults", 0, 1, "Filter End Date")),
    IfDateFilter = if IsDateFilter = "Y" then
        Table.SelectRows(RemoveDateErrors, each [Voucher Entered Date] >= DateFilterStart and [Voucher Entered Date] <= DateFilterEnd)
    else RemoveDateErrors,
    AddForeignKey = Table.AddColumn(IfDateFilter, "FK_PO", each [PO Number] & "_" & Number.ToText([Line Number]) & "_" & Number.ToText([Schedule Number]) & "_" & Number.ToText([PO Distribution Line Number]), type text),
    AddPOSource = Table.AddColumn(AddForeignKey, "IsFromPO", each if [PO Number] = "" then "Non-PO" else "PO", type text),
    MergeAPPmtTerms = Table.NestedJoin(AddPOSource,{"Payment Terms ID"},tblAPTerms,{"Code"},"NewColumn",JoinKind.LeftOuter),
    ExpandAPTermCols = Table.ExpandTableColumn(MergeAPPmtTerms, "NewColumn", {"Group", "Active", "Expected Pay Lag"}, {"Group", "Active", "Expected Pay Lag"}),
    RenamePmtTermCol = Table.RenameColumns(ExpandAPTermCols,{{"Group", "Payment Term Group"}, {"Active", "Active"}}),
    MergePwCCategories = Table.NestedJoin(RenamePmtTermCol,{"Account"},tblPwCAccts,{"GL"},"tblPwCAccts",JoinKind.LeftOuter),
    ExpandPwCCategories = Table.ExpandTableColumn(MergePwCCategories, "tblPwCAccts", {"PwC Category"}, {"PwC Category"}),
    AddSupplierInitialCol = Table.AddColumn(ExpandPwCCategories, "Supplier Initial", each Text.Range([Supplier Name],0,2), type text),
    AddPaymentKey = Table.AddColumn(AddSupplierInitialCol, "Pmt_Key", each [Bank Code] & "_" & [Bank Account] & "_" & [Payment ID], type text),
    AddVoucherKey = Table.AddColumn(AddPaymentKey, "Vchr_Key", each [Business Unit] & "_" & [Voucher ID], type text),
    AddInvoiceKey = Table.AddColumn(AddVoucherKey, "Invoice_Key", each [Supplier ID] & "_" & [Invoice Number] & "_" & Date.ToText([Invoice Date]), type text)

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back