Filter if contains

[kassymov_su]

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

[v-stephen-msft]

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

[Anand24 · Accepted solution]

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

[kassymov_su]

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")))