Forum Discussion

Mahmed1's avatar
Mahmed1
Helper IV
2 years ago
Solved

Not text starts with

Hi

 

what is the best way to have a table where i can have an exemption list but in the mcode have it look at this table to filter out all the codes that does not start with everything in this table

 

ive got code that does it with

each list.contains([code] = tbl_exemption_list)

 

how can I achieve the same this but replace with text does not start with? At the moment im having to keep adding it manually into power query

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    Hi, Mahmed1 

     

            Table.SelectRows(
            your_table, 
            (x) => 
            not List.AnyTrue(
                    List.Transform(
                        exemption_list, 
                        (t) => Text.StartsWith(x[code], t)
                    )
                )
        )

     

    but exemption_list must be a list, not table. I hope you know how to get a list from a column of your table. And buffer this list before usage (outside of Table.SelectRows). 

5 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    Hi, Mahmed1 

     

            Table.SelectRows(
            your_table, 
            (x) => 
            not List.AnyTrue(
                    List.Transform(
                        exemption_list, 
                        (t) => Text.StartsWith(x[code], t)
                    )
                )
        )

     

    but exemption_list must be a list, not table. I hope you know how to get a list from a column of your table. And buffer this list before usage (outside of Table.SelectRows). 

  • Mahmed1's avatar
    Mahmed1
    Helper IV
    2 years ago

    Thank you

    the way i did it was - right click - drill doen column to change to list

     

    that right?

  • Mahmed1's avatar
    Mahmed1
    Helper IV
    2 years ago

    Thank you - ill try and give that a go

    Where you put your table, do you mean refer to previous step or the exemption list table?