The Power Apps With function is another great function to learn & add to your repetoire. It helps to streamline code, make it more readable and increase performance. Once you start using it, you’ll wonder why you didn’t start using it sooner.
This topic was influenced by some questions asked on the Power Apps forums. I’ve provided a couple of good resolutions that use the With function, so thought I’d put this article together to help others.
As per Microsoft Docs, the With function temporarily stores a value or record that can be evaluated. By temporarily, I mean it’s only available for the duration of overall formula execution; there’s no tangible record of it existing otherwise, unlike a collection or variable.
If you’ve worked with SQL Server in the past, you’ll be familiar with temp tables; the Power Apps With function is effectively the same thing. The function is constructed like so:
With( Record, Formula )
The record part of the function is the data we are storing temporarily. The formula part is what we want to do with that data, for example set some variables or create a new row in a data source.
Example data
When possible, I use Dataverse as an example for the data in my articles. For this article, however, I will use SharePoint lists. Why, that will become clear later 😊.
I have three SharePoint lists to support an expense process. The first is a list of venues and the respective meal allowances:
The second is a user database:
Finally, we have a list that’s an amalgamation of the user and respective venue, tracked in an expenses submission list:
In my canvas app, I have two drop down controls – dpdVenue and dpdMealSelect. These will be needed to help get the right values in our Power Fx:
With function to replace multiple lookups
We often see many nested lookups to populate data in some way. Here’s an example block of Power Fx that builds a collection using this technique. The problem with this is that for EVERY value that is needed, there is a call to the data source, which in the example below is 6 calls. This makes the process very expensive:
// Create collection using lookups to external data source
ClearCollect(
colMealBudgets,
{
Venue: LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Title,
Postcode: LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Postcode,
Breakfast: LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Breakfast,
Refreshments: LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Refreshments,
Lunch: LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Lunch,
Dinner: LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Dinner
}
);
Apart from the cost, there is a lot of duplication here. What if the query criteria change? Then a few changes are necessary. Also, it’s not easy to read as there is a lot of text.
With the Power Apps With function, we can completely revise the structure. This has the following advantages:
1- Increase performance; from a UX point of view, anything to improve speed and performance of Power Fx operations is going to be an epic win. Using With, we’ll make the API once, not several times over.
2- Improve readability; anything to make clean & digestible code for you or others to read is also an epic win.
3- Keep it DRY; the age-old developer mantra, Don’t Repeat Yourself. Why write a lookup several times when you only need to do it once.
Let’s use With to perform the lookup instead. We can then reference that temporarily stored data to build the collection:
// Create collection using With and x1 lookup to data source
With(
{
tvData: LookUp(with_MealBudgets, Title = dpdVenue.Selected.Value)
},
ClearCollect(
colMealBudgets,
{
Venue: tvData.Title,
Postcode: tvData.Postcode,
Breakfast: tvData.Breakfast,
Refreshments: tvData.Refreshments,
Lunch: tvData.Lunch,
Dinner: tvData.Dinner
}
)
);
Much cleaner, less expensive and therefore will be quicker to run.
With function to replace multiple filters
In the same way, we can use the Power Apps With function to avoid multiple filters to a data source in favour of a single call. In the following example, I set three variables that each filter the same SharePoint list, but retrieve a different value:
// Set variables by filtering an external data source
Concurrent(
Set(
gvVenue,
Filter(with_MealBudgets,Title = dpdVenue.Selected.Value).Title
),
Set(
gvBreakfastBudget,
Filter(with_MealBudgets,Title = dpdVenue.Selected.Value).Breakfast
),
Set(
gvDinnerBudget,
Filter(with_MealBudgets,Title = dpdVenue.Selected.Value).Dinner
)
);
Let’s reconstruct that using a single filter of the SharePoint list, using the With function:
// Set x3 variables with a single call to the Meal Budgets list
With(
{
tvData: Filter(with_MealBudgets, Title = dpdVenue.Selected.Value)
},
Concurrent(
Set(gvVenueWith, tvData.Title),
Set(gvBreakfastBudgetWith, tvData.Breakfast),
Set(gvDinnerBudgetWith, tvData.Dinner)
)
);
Embedding With functions
The Power Apps With function allows us to capture multiple layers of temporary data. That’s to say, “with this first set of temporary data, process a second set of temporary data, so I can do some more processing”.
In this example we are creating a new record in the Expenses SharePoint list. This is a culmination of data from the User and Meal Budget SharePoint lists, plus a Switch formula to find the correct meal budget based on the selection in our drop down control:
// Create new record in data source with combination of lookups
Patch(
with_Expenses,
Defaults(with_Expenses),
{
Title: LookUp(with_Users,Email = "yoda@starwars.com").Title,
Email: "yoda@starwars.com",
Department: LookUp(with_Users,Email = "yoda@starwars.com").Department,
Role: LookUp(with_Users,Email = "yoda@starwars.com").Role,
Venue: LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Title,
Meal: dpdMealSelect.Selected.Value,
Budget: Switch(
dpdMealSelect.Selected.Value,
"Breakfast",
LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Breakfast,
"Refreshments",
LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Refreshments,
"Lunch",
LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Lunch,
"Dinner",
LookUp(with_MealBudgets,Title = dpdVenue.Selected.Value).Dinner
)
}
);
Let’s improve that block of Power Fx. Firstly, we can create two sets of temporary data; one for Meal Budgets and another for the User. We can then create a second set of temporary data, using the output of our Meal Budgets temporary data to perform the Switch statement logic for the budget field.
The Patch statement to create the new record will then reference our three temporary sets of data; tvMealBudgets, tvUserData & tvBudget:
// Create temp data for Meal Budgets and User
With(
{
tvMealBudgets: LookUp(with_MealBudgets, Title = dpdVenue.Selected.Value),
tvUserData: LookUp(with_Users, Email = "yoda@starwars.com")
},
With(
{
// With Meal dropdown, perform switch on temp Meal Budgets
// data instead of a lookup to data source
tvBudget:
Switch(dpdMealSelect.Selected.Value,
"Breakfast", tvMealBudgets.Breakfast,
"Refreshments", tvMealBudgets.Refreshments,
"Lunch", tvMealBudgets.Lunch,
"Dinner", tvMealBudgets.Dinner)
},
// Create record using data from x3 Withs
Patch(
with_Expenses,
Defaults(with_Expenses),
{
Title: tvUserData.Title,
Email: tvUserData.Email,
Department: tvUserData.Department,
Role: tvUserData.Role,
Venue: tvMealBudgets.Title,
Meal: dpdMealSelect.Selected.Value,
Budget: tvBudget
})
)
);
To help show the flow of data, see the image below:
1- tvMealBudgets (pink) is our temporary data from the Meal Budgets SharePoint list, using a single lookup.
2- tvUserData (green) is our temporary data from the User SharePoint list, using a single lookup.
3- tvBudget (blue) is our temporary data to dynamically retrieve the correct column from the tvMealBudgets output.
Speed test
I decided to run a speed test and prove that using the Power Apps With function is likely to be a lot quicker for processing. In this speed test, I’ll run all three examples one after the other; build collection using lookups, set variables using filter, create a new record.
At the start of the Power Fx block, I’ll set a variable for each method to take note of the start date/time:
// Start processing
Set(
gvStart,Now()
);
Once our various logic has finished running, we can calculate the total run time:
// Stop processing
Set(
gvFinish,
DateDiff(gvStart,Now(),TimeUnit.Milliseconds)
)
Over the course of 10 runs, using the Power Apps With function shows to be quicker. This is expected as doing less calls back to a data source. Whilst these numbers are small here, keep in mind potential gains over larger data sets or if using a higher volume of lookups or filters.
With and delegation
The reason for using SharePoint lists in this example, is they’re very susceptible to delegation issues in Power Apps. Apps will try and pass on the processing load to the data source, but some functions in Power Apps will have a bad time as a result.
Since the Power Apps With function was released a couple of years ago, it’s been quoted many times as a workaround for delegation issues – especially when filtering or retrieving data from SharePoint.
Spoiler alert: it’s not a workaround.
Unfortunately, all it does is remove the warnings in Maker Studio and delegation issues continue to exist. To showcase this, I have a SharePoint list with 4000 items. I’ve manually filtered the list where the Title column equals Question 100, there are four records that match the criteria:
In my Power App, I’ve rigged up the LotsOfData SharePoint list to a gallery. I’ve added a text input control and updated the Items property of the gallery to perform a search. From the image below, we can see that only two records for Question 100 are shown when we know there’s four. More importantly, we see our two indicators of delegation issues – the blue underline and the yellow triangle of death:
Wrapping the data source inside the Power Apps With function masks the issues. There’s no blue underline or yellow triangle of death, but delegation issues persist:
It’s hugely important to remember that any lookup or filtering logic in a With function still needs to adhere to any delegation rules You can use non-delegable functions against the output of the With function – your temporary data, but not to create the temporary data itself. In the image below, I’m performing a delegable filter on the LotsOfData SharePoint list. This means the text searching for Question 100 returns the four expected rows:
I could go on about the Power Apps With function for quite a while, it’s very powerful and something I use where I can. I’ll look to do a more in-depth guide on With at some point, but hoping this serves as an amuse-bouche for now.
Thanks for reading! If you liked this article and want to receive more helpful tips about Power Platform every week, don’t forget to subscribe 😊
Thanks again for sharing. I’ve been using it for a while now but your use cases has helped shed more light and understanding on this power function.
Happy to help as always, Douglas!
That’s for sharing
I’m looking forward to the in-depth guide
Thanks Claus, more in-depth guide planned for 2024
Excellent examples, this is seriously helpful. Thank you
Thanks for the feedback, appreciated!
Nothing is too late to learn. Thanks for sharing. Great article
Thanks, Necdet!
Explanation with great examples helped understand the usage than watching multiple videos. Great article. Thank you so much.