Using a parameter to refer to a column in Power Query

[Petri]

I try to use a parameter to select a (date) column and then filter the latest value in that column. The name of the column varies in source files but is always the first column, so I tried:

FirstColumn = Table.ColumnNames(OurTable){0},

which in this case returns the correct value (=Date). But the following step produces just the headers of the columns with no data rows:

#"Filtered Rows" = Table.SelectRows(OurTable, let latest = List.Max(Table.Column(OurTable,FirstColumn)) in each Table.Column(OurTable,FirstColumn) = latest)

For trouble shooting, I can see that the following line gives the date 30/09/2022:

latest = List.Max(Table.Column(OurTable, FirstColumn)),

 but what is wrong in the Filtered Rows step above?

[ppm1 · Accepted solution]

You can use this syntax in your Filtered Rows step.

= let firstcolumn = Table.ColumnNames(Source){0},
latestdate = List.Max(Table.Column(Source, firstcolumn))
in
Table.SelectRows(Source, each Record.Field(_, firstcolumn) = latestdate)

Replace Source with your previous step name throughout.

Pat

[wdx223_Daniel]

guess

NewStep=Table.LastN(Table.Sort(OurTable,Table.ColumnNames(OurTable){0}))

[Petri]

This seems to be on the right track but I got error:  "1 arguments were passed to a function which expects 2". If I modify the line by adding a number in the end:

NewStep=Table.LastN(Table.Sort(OurTable,Table.ColumnNames(OurTable){0}),3)

it gives me three rows each of which has the latest (30/09/2022) date. But if I try to replace '3' with something similar to the Last.Max in my original post, I get the same issue. I do not know how may rows there are for each date.