Forum Discussion
Solved
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 stop...
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
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