Forum Discussion
How to filter a Table column in Power Query?
From the File combine custom function, you can filter a Record attribute using the following syntax:
each [RecordColumnName]?[AttributeColumnName]?
Below I have a dataset where I grouped the data according to Source.Name, intending only to get the row with the most recent timestamp via the Index column. Is there a way I can filter the Custom table without needing to expand it and applying a filter step afterwards?
Hi,
Yes, you can filter the nested table without expanding it, and in most cases this is preferable because it keeps the transformation within each grouped table.
For example, if you only want the row with the highest Index from each nested table, you can use:
Table.TransformColumns(
#"Grouped rows",
{
"Custom",
each Table.Max(_, "Index")
}
)
or equivalently:
Table.TransformColumns(
#"Grouped rows",
{
"Custom",
each
let
maxIndex = List.Max(_[Index])
in
Table.SelectRows(_, each [Index] = maxIndex)
}
)
Which is more efficient?
Table.Max() is generally the better choice because:
- It directly returns the row with the maximum value.
- It avoids creating an intermediate list (List.Max) and then scanning the table again with Table.SelectRows.
- The code is simpler and easier to maintain.
That said, the actual performance difference is usually small unless you're working with very large nested tables.
Can this be done without nested tables?
Yes. If your goal is simply to keep the latest record per Source.Name, another common approach is:
- Sort the table by Source.Name and Timestamp (descending).
- Remove duplicates on Source.Name.
This avoids creating grouped/nested tables altogether and is often faster, especially if query folding is preserved.
For example:
let
Sorted =
Table.Sort(
Source,
{
{"Source.Name", Order.Ascending},
{"Date created", Order.Descending}
}
),
Latest =
Table.Distinct(Sorted, {"Source.Name"})
in
Latest
Regarding Table.Buffer(), I'd recommend using it only after profiling your query. While it can improve performance in some scenarios by preventing repeated evaluation, it also breaks query folding and loads the buffered table into memory, which can actually degrade performance for large datasets.
If your source supports query folding (e.g., SQL Server, Fabric, Dataverse), the Sort + Distinct approach is often the most efficient. If query folding isn't available, Table.Max() within the grouped tables is a clean and efficient solution.
Hope this helps.
Thanks!
7 Replies
Hi,
Yes, you can filter the nested table without expanding it, and in most cases this is preferable because it keeps the transformation within each grouped table.
For example, if you only want the row with the highest Index from each nested table, you can use:
Table.TransformColumns(
#"Grouped rows",
{
"Custom",
each Table.Max(_, "Index")
}
)
or equivalently:
Table.TransformColumns(
#"Grouped rows",
{
"Custom",
each
let
maxIndex = List.Max(_[Index])
in
Table.SelectRows(_, each [Index] = maxIndex)
}
)
Which is more efficient?
Table.Max() is generally the better choice because:
- It directly returns the row with the maximum value.
- It avoids creating an intermediate list (List.Max) and then scanning the table again with Table.SelectRows.
- The code is simpler and easier to maintain.
That said, the actual performance difference is usually small unless you're working with very large nested tables.
Can this be done without nested tables?
Yes. If your goal is simply to keep the latest record per Source.Name, another common approach is:
- Sort the table by Source.Name and Timestamp (descending).
- Remove duplicates on Source.Name.
This avoids creating grouped/nested tables altogether and is often faster, especially if query folding is preserved.
For example:
let
Sorted =
Table.Sort(
Source,
{
{"Source.Name", Order.Ascending},
{"Date created", Order.Descending}
}
),
Latest =
Table.Distinct(Sorted, {"Source.Name"})
in
Latest
Regarding Table.Buffer(), I'd recommend using it only after profiling your query. While it can improve performance in some scenarios by preventing repeated evaluation, it also breaks query folding and loads the buffered table into memory, which can actually degrade performance for large datasets.
If your source supports query folding (e.g., SQL Server, Fabric, Dataverse), the Sort + Distinct approach is often the most efficient. If query folding isn't available, Table.Max() within the grouped tables is a clean and efficient solution.
Hope this helps.
Thanks!
Hello olimilo
What can work in this case is to apply the filter directly inside the table stored in the column. Instead of expanding and filtering afterwards, you apply the logic at that level.Since you already have an index, you can simply calculate the maximum value inside each sub-table and keep only that row. You can do it like this:Table.TransformColumns( #"Grouped rows", { "Custom", each let maxIndex = List.Max(_[Index]) in Table.SelectRows(_, each [Index] = maxIndex) } )Here you are working directly on the inner table (_), finding the highest index and filtering at the same time.If you want something even simpler, you can use:Table.TransformColumns( #"Grouped rows", { "Custom", each Table.Max(_, "Index") } )This returns the latest record for each group straight away, without needing to expand anything.In practice, the key idea is to apply the filter inside the nested table rather than outside. This keeps the query cleaner and usually more efficient.I understand the syntax is simpler, but would it necessarily equate to being more efficient performance-wise
Table.TransformColumns( #"Grouped rows", { "Custom", each Table.Max(_, "Index") } )compared to this one?
Table.TransformColumns( #"Grouped rows", { "Custom", each let maxIndex = List.Max(_[Index]) in Table.SelectRows(_, each [Index] = maxIndex) } )Furthermore, is there a way to do this without needing to create a nested table? I saw another example that only requires the need to Buffer, Sort and Remove Duplicates but I am unsure as to how efficient the Buffer step would be, as opposed to grouping by ID, adding a group/row index then filtering for the Max/Min Index.
Try this custom column
Table.SelectRows([Custom], each [Index] = List.Max([Custom][Index]))Filters each nested Custom table to return only the row with the highest Index value (latest record)
Table.SelectRows([Custom], each [Index] = List.Max([Custom][Index]))Hi olimilo
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
Hi olimilo
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.
