cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sussan
Helper III
Helper III

How to exclude a row of my sharepoint list

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

1 ACCEPTED SOLUTION

Accepted Solutions
AmDev
Super User
Super User

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

View solution in original post

1 REPLY 1
AmDev
Super User
Super User

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

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,951)