Forum Discussion

chydewf1's avatar
chydewf1
Frequent Visitor
8 years ago
Solved

Filtering row based on format

Hi,   I have a list of part numbers where i want to filter anything out that is not in a particular format.   The format I want to keep is:   XX-#####-X-###   Where x = any letter of the alph...
Need Help
  • chydewf1's avatar
    chydewf1
    8 years ago

    Hi v-huizhn-msft

     

    I think I have solved it - The following seems to work well

     

    = if(mid(Table[Column],11,1)="-"&& mid(Table[Column],9,1)="-"&& mid(Table[Column],3,1)="-",TRUE,FALSE)

     

    Thanks for your help

     

    Chris

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi chydewf1,

Please create a column to get ture if text column CONTAINS specified value "-" using search and text.contains in this thread.

I test it using the sample table and create a calculated column through search function.

sign =
IF ( IFERROR ( SEARCH ( "-", Test[Value] ), -1 ) > 0, TRUE (), FALSE () )




Then you can use the [sign] column to create a new table, slicer or other filters(page/report level filter) to filter your table rows.

Thanks,
Angelia

chydewf1's avatar
chydewf1
Frequent Visitor
8 years ago

Hi v-huizhn-msft

 

Thanks for your reply.

 

It almost works but does not filter this sort of thing:

 0.8X-V175

 

Ideally I would say if the 3rd, 9th and 11th characters are "-" then return TRUE

 

Is there any way I can achieve this?

 

Thanks

 

Chris

 

  • chydewf1's avatar
    chydewf1
    Frequent Visitor
    8 years ago

    Hi v-huizhn-msft

     

    I think I have solved it - The following seems to work well

     

    = if(mid(Table[Column],11,1)="-"&& mid(Table[Column],9,1)="-"&& mid(Table[Column],3,1)="-",TRUE,FALSE)

     

    Thanks for your help

     

    Chris