When to use Table.Buffer

Hi campelliann ,

This is an enormous question with many, many variables. However, very broadly, once you understand what Table.Buffer and List.Buffer do, this will often inform you of when is a good time to use them.

At their core, these functions load the entire target into memory. Therefore, one key use is to use them when Power Query would otherwise make multiple scans of the target.

For example:

If you want to do a conditional join in PQ (in this example to merge values from an SCD table), you may find yourself creating a column something like this to achieve it:

= Table.AddColumn(
  previousStep,
  "columnName",
  (OT) => Table.SelectRows(
    Table2,  // The 'Target'
    each OT[Field] = [Field]
      and OT[dateField] >= [table2StartDate]
      and OT[matchField2] <= [table2EndDate]
    ){0}[valueToMerge],
  type number
)

In this instance, PQ will need to keep rescanning Table2 for the conditions on each row. Therefore, if we buffer Table2 in the Table1 query (T2Buffer = Table.Buffer(Table2) ), then swap the target from Table2 to T2Buffer, PQ can just hit the memory for what it needs each time.

List.Buffer example:

If you want to filter a table by the values in another table, you may find yourself righting code like this:

Table.SelectRows(
  TableToFilter,
  each List.Contains(
    FilterTable[ColumnWithFilterValues]),  // The 'Target'
    TableToFilter[ColumnName]
  )
)

In this instance, PQ will scan FilterTable in order to check the values against each row value in TableToFilter.

Therefore, if you buffer the FilterTable values first, PQ can just hit the memory for these values. You can do this inline (we created a separate step for this previously), like this:

Table.SelectRows(
  TableToFilter,
  each List.Contains(
    List.Buffer(FilterTable[ColumnWithFilterValues])),  // The 'Target'
    TableToFilter[ColumnName]
  )
)

This example is particularly important when applying this technique on foldable sources, as doing it this way means you can stream non-foldable lists to foldable queries and maintain folding 👍

Hope this helps?

Pete