Forum Discussion
Filetring table based on the values in last column
How can indicate that it's the last column that should be filtered without hard coding the name of the column. The problem is that name of the last column is different with each report and so it stops working - there is no column name that matches hard coded value.
Here is the code:
let
Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(Filepath & "\report\file.xlsx"), null, true),
report_Sheet = Source{[Item="report",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(report_Sheet,1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
KeepLastColumn =
Table.SelectColumns(
#"Promoted Headers",
{ "some1", "some2", "some3", "some4", "some5", "some6", "some7", "some8", List.Last ( Table.ColumnNames ( #"Promoted Headers" ) ) }
),
#"Filtered Rows" = Table.SelectRows(KeepLastColumn, each ([#"2020-10-31_amount"] = 0))
in
#"Filtered Rows"I tried changing it to:
#"Filtered Rows" = Table.SelectRows(KeepLastColumn, each (List.Last ( Table.ColumnNames ( #"Promoted Headers" ) ) = 0)) but the table comes out empty.
In summary I need to keep columns named "some[1-8]" and the last one. Then, I need to keep only rows that have 0 in the last column (last column name differs with each report).
another option following up on the approach you had initially (extracting the name):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGmSrE60UpGQBYyNgCLGgNZyNgcLGoCZCFjC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [some1 = _t, some2 = _t, some3 = _t, some4 = _t, another = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"some1", Int64.Type}, {"some2", Int64.Type}, {"some3", Int64.Type}, {"some4", Int64.Type}, {"another", Int64.Type}}), nameLastCol_= List.Last(Table.ColumnNames(#"Changed Type")), filtered_ = Table.SelectRows(#"Changed Type", each Record.Field(_,nameLastCol_)=0) in filtered_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
6 Replies
another option following up on the approach you had initially (extracting the name):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGmSrE60UpGQBYyNgCLGgNZyNgcLGoCZCFjC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [some1 = _t, some2 = _t, some3 = _t, some4 = _t, another = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"some1", Int64.Type}, {"some2", Int64.Type}, {"some3", Int64.Type}, {"some4", Int64.Type}, {"another", Int64.Type}}), nameLastCol_= List.Last(Table.ColumnNames(#"Changed Type")), filtered_ = Table.SelectRows(#"Changed Type", each Record.Field(_,nameLastCol_)=0) in filtered_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thank you, that is exactly what I needed. Could you please explain the usage of underscore "_" as first parameter in
Record.Field(_,nameLastCol_) or if you could point me to some kind of documentation that would be great too, thanks!the underscore is the input parameter passed to the evaluating function that decides whether to keep the row when filtering. It is a record with the contents of the current row, one field per column, named after the columns. When you write
Table.SelectRows(Table3, each [Col1] = 3)the evaluating function checks whether [Col1] for that record (the current row) equals 3. Strictly, it should be _[Col1] = 3 but [Col1] is actually short for _[Col1].
Check this out, it explains it fantastically well:
https://www.excelguru.ca/blog/2018/01/09/each-keyword-power-query/
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi Kleszko23
If it is always the last column, it can be done as follows. Place the following M code in a blank query to see the steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGmSrE60UpGQBYyNgCLGgNZyNgcLGoCZCFjC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [some1 = _t, some2 = _t, some3 = _t, some4 = _t, another = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"some1", Int64.Type}, {"some2", Int64.Type}, {"some3", Int64.Type}, {"some4", Int64.Type}, {"another", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each List.Last(Record.ToList(_)) = 0) in #"Filtered Rows"Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Your approach should work. I tried to replicate it, and at first Table.ColumnNames was showing weird behavior for me too. It was returning True/False values instead of a list of column names. I suspect you are seeing same behavior. I added another step (renamed a column) then deleted it and now Table.ColumnNames is working again. Can't explain why.
I would consider using List.Last(Table.ColumnNames()) to rename the last column and then filter that new column names. Having the same name in the last columns will be needed if you intend to append the data.
Regards,
Pat
and another variation following up on your initial approach, with Expression.Evaluate() to circumvent the issue you were bumping up against:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSQcGmSrE60UpGQBYyNgCLGgNZyNgcLGoCZCFjC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [some1 = _t, some2 = _t, some3 = _t, some4 = _t, another = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"some1", Int64.Type}, {"some2", Int64.Type}, {"some3", Int64.Type}, {"some4", Int64.Type}, {"another", Int64.Type}}), nameLastCol_= List.Last(Table.ColumnNames(#"Changed Type")), filtered_ = Table.SelectRows(#"Changed Type", each Expression.Evaluate("[" & nameLastCol_ & "]=0",[_=_])) in filtered_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
