Forum Discussion

barbforsman's avatar
barbforsman
Resolver I
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

Could you please share what is the exact value in the column ( is it just 'Cancel' or value contains 'Cancel') and also share what filter you have used in Query level?

  • barbforsman's avatar
    barbforsman
    Resolver I
    8 years ago

    This is the report level filter

     

    And this is the filter on the query:

     

    Help will be much appreciated!

     

  • barbforsman's avatar
    barbforsman
    Resolver I
    8 years ago

    This is the report level filter

     

    And this is the filter on the query:

     

    Help will be much appreciated!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Did you try this option? This works for me.

       

       

      And then apply the filter

       

      • barbforsman's avatar
        barbforsman
        Resolver I
        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