power apps assignment operator

No Ads, No Fluff, Just Power Apps Stuff

Power Apps Standards
Power Automate Standards
Power Apps Functions
Power Automate Functions
Collections Cookbook
Free Power Apps Icons

The Complete Power Apps Functions List

Welcome to The Complete Power Apps Functions List. On this page you will find a categorized list of 150+ Power Apps functions with links to the official documentation. Experimental functions like PDF are also included. Power Apps already has an official formulas reference so why did I do this? A few reasons:

I wanted a functions cheat sheet where I could quickly see all the functions and how to write them
Organizing all of the functions into categories makes it easier to discover new ones
Gaps in the official Power Apps documentation can be filled-in with my own commentary

I hope you enjoy the Power Apps Functions List.

AI Functions

Color functions, datasource functions, date & time functions, error functions, forms & controls functions, information functions, logical functions, math functions, navigation functions, offline functions, other functions, signals functions, statistical functions, table functions, testing functions, text functions, trigonometry functions, variable functions, web functions.

* Experimental functions must be enabled in the Power Apps upcoming features menu

Did You Enjoy This Article? 😺

Subscribe to get new Power Apps articles sent to your inbox each week for FREE

Enter your email address

If you have any questions about The Complete Power Apps Functions List please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.

Great collection, thanks!

Awesome post, as usual. Thanks, Matt.

Thank You for this effort

Thank you SO much Mathew ! without you i can’t learn power apps ,i am really greatful to you

We talked about it on Twitter, didn’t we? Thank you!!

Thanks for this precious stuff.

Awesome job Matthew! I have added docs for the Index function and RandBetween function . Index hadn’t finished rolling out worldwide yet which is why it was delayed. RandBetween was just an oversight that I’m happy to fill. Love the categorized list, that may be something we need to do with the docs too.

Glad you liked the categorized list. I tried to stick as close to Excel as possible then add my own categories to fill-in the rest.

Thanks to your team for publishing the RandBetween function and the Index function documentation! I know the documentation team works hard – their job is never done and they have to localize everything.

More clearence, impossible. Tx.

It is a great source for a beginner like me, But it would be wonderful if you put examples & images for every function, then the idea would be more clear to understand for any person. But this collection is great.

This page is definitely something I intend to expand upon. I think that examples and images would work well if I turned this into a printable cheat sheet with landscape orientation (something I am considering). Another thing I am wanting to do is make a “quick guide” to each section. I’ve already done guides for dates, math, stats and text. Those would just be a matter of linking up.

This current iteration works well as a way to discover functions you need. The trade-off is, as you add more data it becomes something else.

Thanks for the thoughtful comment!

In the above page “Information Function” is not correctly page referenced using #tag and hence it is not navigating.

Thank you for pointing that out! I’ve fixed it now and the page will be updated once the cache refreshes.

Much needed, thanks!

PS: I think that the anchor link “Logical Functions” does not work

Thanks for the tip! I’ve fixed the anchor and it should work properly as soon as my website cache refreshes 🙂

Matt, this was a lot of work for you! And I am so impressed. Thank you, thank you, thank you! With your permission, I would like to embed this into a Teams tab just for me. (Yes, I have a Team Valerie team for just me, my admin account, and my test account.

Thank you! What a gift!!!

Great stuff. Thanks for compiling this!

Thank you for doing this Matt. I’ve been meaning to go through the list of functions and what they do to make sure I fully understand each one and don’t miss out on any of the less common, but powerful ones. I really appreciate the time you took to get this list so organized!

Is it possible to get device parameters?

The signals function group contains all data generated by the device.

Thank you for so much. I’m the beginner, so this site is so useful. I would like to recommend your site to everyone in Japan.

What would you use for ‘contain’?

The ‘in’ operator is what you should use for a logic statement with the condition contains.

Hello Matthew, is there an FX function in powerapps similar to Ticks() function in power automate ? Thank you so much for your content Boudani

Can you please point me to a webpage that explains the ticks function? I’ll let you know the equivalent in Power Apps if it exists.

Hello Matthew, thank you for your Response.

Power Automate: ticks Function – Manuel T. Gomes (manueltgomes.com)

Reference guide for expression functions – Azure Logic Apps | Microsoft Learn

thank you for your content, it helped me a lot in learning Powerapps.

have a nice day Boudani

Microsoft define the ticks function this way: Returns the number of ticks, which are 100-nanosecond intervals, since January 1, 0001 12:00:00

Maybe you can use a DateDiff to do it? DateDiff(Date(1,1,1), Now(), Milleseconds) * 1000000

Power Apps doesn’t really do nano-seconds though. I think milleseconds is the furthest it goes.

Hi Matthew, you are so right, this way is enough. Thank you so much. Have a nice day Boudani

michelcarlo

Microsoft 365, Power Platform and SharePoint related thoughts

Alternative to the ‘in’ operator for Power Apps when filtering SharePoint data

Configuration
Development
Microsoft 365
SharePoint Online

Many of you may already have faced some delegation limitations when using SharePoint as a data source for your canvas apps.

When the operation or filter is delegable, it means the query results are processed by the data source and returned to Power App rather than being processed locally in the App, with a maximum of 2000 items to be returned per call. For performance reasons, the default data row limit for non-delegable queries is only 500 items per call but can be increased to 2000 items. If a data source has more than the configured limit and a query with non-delegable operators or filters is executed, inaccurate results will likely be retrieved.

An operator that is not delegable and is missed is the ‘in’ operator (as a test of membership).

I have seen some approaches around the web to simulate the behaviour of an ‘in’ operator in Canvas apps with SharePoint as a data source, but some of them are not based on dynamic selection or are based on hiding or showing content and not based on techniques to filter data differently.

An example of content to be filtered where issues may happen

In the example below, a demo list was created with almost 60000 items.

There are more than 2000 items related to each Ireland county (it’s a lookup field to another list) so that it’s possible to visualise how the filters with delegation issues don’t work properly and how the approach shown later in the article can help to overcome this limitation.

In the following examples, this list will be filtered by the County field, based on a multiple selection combo box. The idea is to filter list items that have any of the chosen filters as County.

Filter example with the delegation issues: “in” Operator

When default ‘in’ operator based in a combo box selection is used in a formula to filter SharePoint list items, the ‘in’ operator is highlighted with the delegation warning:

In all Power App examples used in this post, the count of items will be shown in a label on top of the gallery for better understanding. When selecting a county among the first results returned by the data source, the proper results are retrieved (limited to 2000 items). But if any other county is selected, zero items are returned.

This is due to the issue explained above, Power Apps gets the first results from the data source (first 2000 items from the SharePoint list), and then applies the filter locally in the records that were retrieved. As ‘Cavan’ was not among the first 2000 results, nothing is retrieved.

How to implement a membership filter what will work as the ‘in’ operator

The idea of this approach is to iterate through the chosen filters using a ForAll function. Then query the data source using the delegable equals ‘=’ filter for each of the filter values and return the query results (it works like a ‘map’ operator).

By only using the ForAll and the combination of the filter, it works but the results are obtained in groups (per county filtered). This could be easily used with nested galleries, as in the picture below (the inner gallery uses the child records collection as items):

To make the results become a flat collection, we can use the Ungroup function. By doing this, the result of the ForAll loop can be ungrouped and all items will then lie at the same level. The limitation is that a maximum of 2000 items can be retrieved per data source call. The batch load that normally works for galleries when they are scrolled will not work when using the formula below. This is due to the results being retrieved and generated once, as even the Filter function is delegable, ForAll is not.

By using the above formula as the items property for a gallery, when filtering the list by multiple filters thousands of values can be retrieved.

This approach also has the limitation of the maximum of 2000 items per specified value in the collection of values to be compared as a filter. If the real number of items in the list is more than 2000 obtained by PowerApps, only the first 2000 among the matching records will be retrieved.

For example, the following SharePoint list view for the demo list is filtered by County ‘Cavan’. The item count has more than the results retrieved by the app (2294).

If the list is filtered by County ‘Cavan’ and status ‘Active’, 803 items are found:

If filtered by two counties (‘Cavan’ and ‘Carlow’) and Status ‘Active’, 1806 items would be found:

By applying additional filters in the Power App formula, the query will be more precise, and the right results are obtained, for example, you could modify the formula to formula mentioned earlier to filter by Status ‘Active’:

If this formula is used as the items property in a filtered gallery, the correct number of items is loaded:

Conclusion:

The approach demonstrated in this post works better than using the ‘in’ operator but has some drawbacks, so use it carefully.

For each item used as a filter in the loop, a data source call will be executed. Which means that for a big collection of filters it may not work fine due to the number of items retrieved, and the number of calls done.

Imagine a case with 20 values selected as filter and 2000 items retrieved per item…40000 items would be returned in the full operation, which would be a heavy operation for both the app and the data source. For better performance, it is recommended to use some delegable filters in combination with this technique in the Filter function call or reduce the number of items in the collection used in the ForAll function.

Even though SharePoint online has automatic indexes activated by default on new lists, which allows your queries to overcome the SharePoint 5000 items threshold by filtering on indexed fields, there is a hard limit of 20 indices per list. Plan your indices accordingly so you make sure the most important ones will be set up.

18 comments

[…] Alternative to the ‘in’ operator for Power Apps when filtering SharePoint data […]

Is this solution applicable for membership filter ‘in’ operator in sql server?

Hi Frank, I haven’t tested it using SQL, but the formulas would be similar for other underlying data sources as SQL/CDS.

Thank you for this great tutorial! How would you go about filtering a SharePoint multiple selection column instead of it being a multiple selection combo box in PowerApps? So for example, let’s say I have a multi selection choice column in SharePoint where you can choose Dog, Cat, and/or Turtle. Then in PowerApps, I have a single selection dropdown that have these same values but I use this dropdown to filter the list (in a gallery) to see all the rows that contain Dog, Cat, or Turtle in the multi selection choice column. How would I do this without using the “in” operator? Hopefully this makes sense.

Hi Stephen,

Thanks for the feedback!

Unfortunately there is no delegable way to filter a multi value column operation as at the moment for SharePoint multi-value column filters are not delegable…so we would have to stick with the ‘in’ operator which is not delegable or use a trick with Filter/LookUp function (which would also not be delegable for this type of field)… Some options: Filter(YourList, CurrentFilterValue in MultiValueField.Value ) Filter(YourList, !IsBlank(LookUp(MultiValueField,Value = CurrentFilterValue))) Filter(YourList, !IsEmpty(Filter(MultiValueField,Value = CurrentFilterValue)))

The filters mentioned can be used in combination with this tutorial techinique, but the only thing is the delegation issue, which can be a problem to you.

Ah I see… That’s unfortunate… What would be the best alternative to multi-value columns in SharePoint that would be delegable but provides similar functionality?

Hi Stephen, I still haven’t found an alternative 😂

I have been searching for a solution for this for a while now.

The following video should provide what you need.

https://www.youtube.com/watch?v=44j2VRbdWjk

Thanks for the suggestion! I have actually come across that video before, however, when I try to implement it, I find that the gallery sometimes does not load/display items that meet the filtering criteria. I believe this has to do with the fact that the gallery loads 100 items at a time, so if the first item that meets the filtering criteria is in the 101st position, the gallery will be completely blank. That was my experience with it. Curious to hear if you have had a similar experience with missing items or have a solution.

Hi, Can you please recommend how to remove duplicate values on the result of ForAll or Ungroup. In my case some of the values returned by each Filter operation are same. After collecting all results I want to remove duplicates. Can you please recommend how to do it.

Ex: First filter expressions returns “A”, “B”, “C”. Second call to filter returns “A”, “D”. If I use current formula result after ungroup is “A”, “B”, “C”, “A”, “D”. Note “A”, is repeated. I want the final result to be “A”, “B”, “C”, “D”

Can you please help.

Hi, You can use the ‘Group’ function to obtain distinct results, for example group the results by the values you need, and then remove the grouped column from the output using the ShowColumns function to access only the columns you need from your source/collection.

Many thanks for this tutorial, I’ve found it very useful. However, as I am fairly new to Powerapps I am a little stuck on the syntax required to multiple comboboxes, i.e. I have the comboboxes cmbUser and cmbRegion which I want to use to apply filters in the gallery. I assume I would need to call RenameColumns twice for this, but I’m at a loss as to how to do this? Any help would be much appreciated, thanks.

Hey Matt, no problem. Hope it is helpful.

One idea you can use is to do the filters separately in two statements and then get the distinct results… This other post might help (to merge two collections of items and get distinct results): https://michelcarlo.com/2021/04/28/power-apps-merge-tables-collections-and-get-distinct-records-without-using-collect/

I tried this and it works great with a SQL dataset.

Hi Tony, great to hear this😁

Hello, this formula has worked for me in many cases, thanks for that, when I use it when filtering a collection, I don’t know why it shows me the records that meet the filter with information, but it also shows me the records that do not meet the filter. the filter but without information, for example I have a collection of 20, and of these 20, 10 meet the filter, this formula brings me the 10 that meet the filter showing information about it, and in the same way it brings me the other 10 blank No information, but it shows it in the gallery. Is there a way to fix that?

Hello, can you share the exact formula you used and more details about your values?

Comments are closed.