How to exclude a row of my sharepoint list

[Sussan]

Hi,

How can I exclude a row of my sharepoint list to be considered? Currently I am using this formula: 

Something that says TotalMeals besides "Milan" row and "Paris" row  in the SharePoint list. How should I then update the below to reflect this?

"The most meals packed are in: " &
First(Sort(FullData, TotalMeals, Descending)).Location2 &
" where " &
Text(First(Sort(FullData, TotalMeals, Descending)).TotalMeals, "#,###") &
" have been packed!"

Best,

Su

[AmDev · Accepted solution]

Hi @Sussan 

You could write the query like this, adjusting the nr of non Milan Paris locations in the filter query. Rather than duplicate large chunks of code, we use the with function to create a variable which is only used/available in the context of this expression (i.e. 'varwFilter' in this case. 

Note, the reason it is filtered like this is to ensure the query is delegable to SharePoint.

With(
    {varwFilter: 
    First(
        Sort(
            Filter(
                FullData, 
                Location2 = "Other Location A" ||
                Location2 = "Other Location B" ||
                Location2 = "Other Location C" ||
                Location2 = "Other Location D" ||
                Location2 = "Other Location E" ||
                Location2 = "Other Location F"
            ),
            TotalMeals, 
            Descending
         )
     )},

"The most meals packed are in: " & varwFilter.Location2 & " where " & Text(varwFilter.TotalMeals, "#,###") & " have been packed!"
)

However, if the above approach would result in the need to list out loads of Location2 options and provided the nr of rows in your data source are below the currently set data row limit, you could just use the following simpler, but non-delegable query:

With(
    {varwFilter: 
    First(
        Sort(
            Filter(
                FullData, 
                Location2 <> "Milan" &&  Location2 <> "Paris"
            ),
            TotalMeals, 
            Descending
         )
     )},

"The most meals packed are in: " & varwFilter.Location2 & " where " & Text(varwFilter.TotalMeals, "#,###") & " have been packed!"
)

Hope this helps