Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

report level filter vs. filter on query

I have a Report Level Filter on a report on my CRM Salesorders, "Name does not contain Cancel".  I want to change that so the filter is on the query itself, rather than on the report, but my values c...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Got it. Power Query is case sensitive. You have given does not contain "Cancel" so records with "Cancel" will filtered, but not "CANCEL". Please use the below formula to make sure all CANCEL/ Cancel gets filtered.

     

     

    = Table.SelectRows(#"Changed Type", each not Text.Contains(Text.Upper ([Status]), "CANCEL"))

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Did you try this option? This works for me.

 

 

And then apply the filter

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thank you - I tried this but my canceled orders are already filtered out. 

I do need to filter out orders that have Cancel in the name field as we do end up with a few orders in an Invoiced Status but end up Canceled.  I've included a screenshot from an advanced query in CRM showing this

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Got it. Power Query is case sensitive. You have given does not contain "Cancel" so records with "Cancel" will filtered, but not "CANCEL". Please use the below formula to make sure all CANCEL/ Cancel gets filtered.

     

     

    = Table.SelectRows(#"Changed Type", each not Text.Contains(Text.Upper ([Status]), "CANCEL"))