Forum Discussion

kassymov_su's avatar
kassymov_su
Frequent Visitor
4 years ago
Solved

Filter if contains

Hi All,

 

While filtering having some issues. 

   M:

      = Table.SelectRows(#"Renamed Columns1", each ([Progress] <> 1 and Text.Contains([ID],"A")))

   Error:

       Expression.Error: We cannot convert the value 1 to type Text.
       Details:
       Value=1
       Type=[Type]

 

Error because column [ID] contains not only text but also numbers

 

Need Help
  • Anand24's avatar
    Anand24
    4 years ago

    Hi kassymov_su ,

    Since columns Progress and ID are of different datatype and you have used in the same M query code line, you are receiving that error.

    Bifurcating it into 2 different steps should resolve this:

        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Progress] <> 1)),//For Progress NOT EQUALS 1
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([ID], "A")) // FOR ID NOT CONTAINS "A"

     PFA .pbix with solution. You can go to advanced editor and check.

     

    Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

    Proud To Be a Super User !!!
    LinkedIn

3 Replies

  • kassymov_su's avatar
    kassymov_su
    Frequent Visitor
    4 years ago

    Also can somebody tell me if I can filter the same but not contain "A"

     

    = Table.SelectRows(#"Renamed Columns1", each ([Progress] <> 1 and Text.Not.Contains([ID],"A")))

  • Anand24's avatar
    Anand24
    Super User
    4 years ago

    Hi kassymov_su ,

    Since columns Progress and ID are of different datatype and you have used in the same M query code line, you are receiving that error.

    Bifurcating it into 2 different steps should resolve this:

        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Progress] <> 1)),//For Progress NOT EQUALS 1
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.Contains([ID], "A")) // FOR ID NOT CONTAINS "A"

     PFA .pbix with solution. You can go to advanced editor and check.

     

    Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

    Proud To Be a Super User !!!
    LinkedIn
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi kassymov_su ,


    Could you tell me if your problem has been solved?
    If it is, kindly Accept it as the solution. More people will benefit from it.
    Or you are still confused about it, please provide me with more details about your problem.


    Best Regards,
    Stephen Tao