Forum Discussion
Filetring table based on the values in last column
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
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
