Showing results for 
Search instead for 
Did you mean: 
Kudo Collector
Kudo Collector

Find next available number in a list?

I need to find the next available number in a list. This is not the same as finding the highest number and adding 1. For example, I have the following numbers in my database:

  • 1000011
  • 1000045
  • 1005646
  • 1006565
  • 1568795
  • 1894834

The first and last digit are meaningful, but the middle 5 are not. So the "core numbers" would be:

  • 00001
  • 00004
  • 00564
  • 00565
  • 56879
  • 89483

In Excel, I'd simply use PowerQuery to generate a list of numbers 00001 - 99999, then do an antijoin to only keep numbers in my big list that aren't in the original list, then pick the minimum number. Which, in this case, would be 00002.


If that were added to the database then, 00003 would be next, then 00005, as an 00004 already exists.


Getting the core number in PowerApps is easy - the MID() function.


I cannot figure out how to do the logic though to start moving through those numbers to find the first available number.


Ideas where to start?


Wow! Kris and Carlos, PowerMaestros!


Think i'll just hang out over here in the corner, and bow reverently!


All, these are some very good suggestions. I need to review today and this weekend and wrap my head around what is being done. Thanks so much. I will follow up with results or further questions. Super community here!

Helpful resources

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (4,042)