Forum Discussion
Using a parameter to refer to a column in Power Query
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?
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
3 Replies
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
guess
NewStep=Table.LastN(Table.Sort(OurTable,Table.ColumnNames(OurTable){0}))
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.
