Forum Discussion

Kleszko23's avatar
Kleszko23
New Member
5 years ago
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...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    Kleszko23 

    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 

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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