You know those days when you get stuck on a simple problem? When I recently extended one of our COSMO CONSULT Canvas Apps for our Self Service Management, I had such a day – such a problem:

My custom connector to my API provided a list of Azure DevOps Artifacts. Nothing very special, just a list of records. Each record had the app name, version and its URL. You can see an example collection in the following snippet, assigned to “artifacts” collection in my “App.OnStart” trigger:

OnStart: |-
     =ClearCollect(
         artifacts,
         Table(
             {
                 name: "App 1",
                 version: "1.0.2.0",
                 url: "https://my.blob.azure.com/artifacts/app1.zip?sig=144..."
             },             
             {
                 name: "App 2",
                 version: "2.0.19.0",
                 url: "https://my.blob.azure.com/artifacts/app1.zip?sig=144..."
             },
             {
                 name: "App 1",
                 version: "2.0.8.0",
                 url: "https://my.blob.azure.com/artifacts/app1.zip?sig=144..."
             }
         )
     );

First I have assigned my collection to my data table:

Items: =artifacts

My result was:

As you see, my list was totally unsorted. OK, nothing simpler than this in PowerApps. You know we can use a formula to solve this issue. Let’s sort the list by app name and version number. The app name can be sorted in ascending order, with the latest version at the top of the list. Therefore I decided to sort the version in descending order.

Items: =SortByColumns(artifacts, "name", Ascending, "version", Descending)

My result was good but not perfect. The sorting of the version numbers was incorrect:

This was, because the formula sorted by text and not by major, minor, build and revision. Well, to solve this problem I could change my API and provide a correct sorted list. To be honest – this is not an option 🙂

Or, I can do a little bit transforming in my list and add columns for major, minor, build, and revision to my list. Let’s do this! My changed formula is now:

Items: |-
     SortByColumns(
         AddColumns(artifacts, 
             "major",    Value(Last(FirstN(Split(version & ".0.0.0.0", "."), 1)).Result),
             "minor",    Value(Last(FirstN(Split(version & ".0.0.0.0", "."), 2)).Result),
             "build",    Value(Last(FirstN(Split(version & ".0.0.0.0", "."), 3)).Result),
             "revision", Value(Last(FirstN(Split(version & ".0.0.0.0", "."), 4)).Result)
         ), 
         "name",     Ascending, 
         "major",    Descending,
         "minor",    Descending,
         "build",    Descending,
         "revision", Descending    
     )

To avoid problems with short version numbers, I have added the text “.0.0.0.0”. The trick is to split the version number text and store the major, minor, build and revision part in an added column. As result I can sort a 4 independent number columns and my list is sorted as expected:

Summary

This small example shows how easy you can transform and modify data in your PowerApp.

A version number is nothing more than the concatenation of numbers separated by “.”. Accordingly, a version number as text can be decomposed into its parts again. Finally you can compare the value of these parts to sort a list in the right order.