Distinct( Table, Formula )

ClearCollect(numbers,1,2,2,3,1,3,4);

// numbers = [1,2,2,3,1,3,4]

ClearCollect(uniqueNumbers,Distinct(numbers,Value));

// uniqueNumbers = [1,2,3,4]

ClearCollect(
    products,
    { Product: "Apple Print Shirt", Category: "Shirts", Price: "$19,99"},
    { Product: "Slim Fit Jeans", Category: "Jeans", Price: "$50,00"},
    { Product: "Straight Jeans", Category: "Jeans", Price: "$76,50"},
    { Product: "White Sneaker", Category: "Shoes", Price: "$49,29"}
);

Let’s create a simple gallery!

Now we want to filter products by category by clicking on a category in a sidebar like this:

The Items property of the gallery for the sidebar uses the Distinct function. We also use a Distinct Sort function combination.

Sort(Distinct(products,Category),Value)

The Items property of the gallery for the products simply looks like this:

Filter(products,Category = CategoriesGallery.Selected.Value)

As you can see, the Distinct function can be very useful!

Nevertheless, keep in mind that the Distinct function is primarily used for working with datasets that are not necessarily already unique. If you are dealing with a dataset that inherently contains only unique values (e.g., primary keys in a database), using Distinct may not be necessary.

1. Navigate to the SharePoint site you want to delete and click on the settings symbol

   

   Click on settings symbol

2. Click on ‘Site information’

   

   Click on Site infotmation

3. Click on ‘Delete site’

   

   Click on Delete site

4. Confirm Delete by clicking on ‘Delete’

   

   Confirm Delete by clicking on Delete

In this article, I’ll introduce you to the sort function and will provide a step-by-step guide on how to use the function in a Microsoft flow.

Power Automate Sort Function

Order elements within a collection. The objects within the collection can be sorted based on any key with a simple data type.

Syntax

sort([<collection>], <sortBy>?)

Input parameters

• collection (mandatory): The collection to be sorted.
• sortBy (optional): The key of the property that should be used for sorting the collection.

Return value

• Returns the sorted collection.

Power Automate Sort Expression Examples

The most simple examples of a sort expression for sorting an array of numbers:

sort(createArray(3,1,2))
=> [1,2,3]

Power Automate sort reverse order (descending order)

To sort an array in reverse order, you need to sort first in ascending order and then reverse the order with the Power Automate reverse function.

reverse(sort(createArray(3,1,2)))
=> [3,2,1]

Using reverse alone is not enough, because the reverse function simply reverses the order without sorting.

Power Automate Sort Object by column

We have this object as input and want to sort it by last name:

{
  "persons": [
    {
      "firstname": "Sally",
      "lastname": "Smith"
    },
    {
      "firstname": "John",
      "lastname": "Doe"
    }
  ]
}

You can use the following sort expression for this:

sort(variables('persons')?['persons'],'lastname')=> [3,2,1]

You will get a sorted object:

[
  {
    "firstname": "John",
    "lastname": "Doe"
  },
  {
    "firstname": "Sally",
    "lastname": "Smith"
  }
]

How To Use The Power Automate Sort Function

In this article, I’ll introduce you to the chunk function and will provide a step-by-step guide on how to use the function in a Microsoft flow.

Power Automate Chunk Function

Divide a string or array into equal-sized segments.

Syntax

chunk('<collection>', '<length>')
chunk([<collection>], '<length>')

Input parameters

• collection (mandatory): The collection or string to be divided into chunks.
• length (mandatory): The size of the resulting chunks.

Return value

• Returns an array with the chunks in the defined size.

Power Automate Chunk Expression Example

// chunk expression string example
chunk('aaabbbcc', 3)
=>
[
  'aaa',
  'bbb',
  'cc'
]

// chunk expression collection example
chunk([1,1,1,2,2,2,3], 3)
=>
[
  [1,1,1],
  [2,2,2],
  [3]
]

// chunk expression referencing a variable
chunk(variables(‘text’), 3)

How To Use The Power Automate Chunk Function

In this article, I’ll introduce you to the reverse function and will provide a step-by-step guide on how to use the function in a Microsoft flow.

Power Automate Reverse Function

Invert the arrangement of elements in a collection. Utilizing this function in combination with sort() enables sorting a collection in descending order.

Syntax

reverse([<collection>])

Input parameters

• collection (mandatory): The collection to be reversed.

Return value

• Returns the given collection in reverse order.

Power Automate Reverse Expression Example

reverse(createArray(1,2,3))
=> [3,2,1]

// reverse expression with variable
reverse(variables('myCol'))

How To Use The Power Automate Reverse Function

The take function does the opposite of the skip function.

In this article, I’ll introduce you to the take function and will provide a step-by-step guide on how to use the function in a Microsoft flow.

Power Automate Take Function

The Take function in Power Automate enables the extraction of a custom quantity of elements from the start of a collection.

Syntax

take('<collection>', <count>)
take([<collection>], <count>)

Input parameters

• collection (mandatory): The collection that you want to retrieve items from the beginning.
• count (mandatory): The number of items that should be retrieved from the beginning of the collection.

Return value

• A string or array consisting of a specified number of elements taken from the beginning of the original collection.

Power Automate Take Expression Example

// take the first two elements
skip(createArray(1,2,3,4,5),2)
=> 1,2

// reference a variable in a take expression
skip(variables('myCol'),2)

How To Use The Power Automate Take Function

The skip function does the opposite of the take function.

In this article, I’ll introduce you to the skip function and will provide a step-by-step guide on how to use the function in a Microsoft flow.

Power Automate Skip Function

The Power Automate Skip function returns a modified collection by removing a specified number of elements from the start and returning the remaining elements.

Syntax

skip([<collection>], <count>)

Input parameters

• collection (mandatory): The collection that you want to remove items from the beginning.
• count (mandatory): The number of items that should be removed from the beginning of the collection..

Return value

• Returns the given collection without the removed items at the beginning of the collection.

Power Automate Skip Expression Example

// skip the first two elements
skip(createArray(1,2,3,4,5),2)
=> 3,4,5

// reference a variable in a skip expression
skip(variables('myCol'),2)

How To Use The Power Automate Skip Function

In this article, I’ll introduce you to the variables function and will provide a step-by-step guide on how to use the function in a Microsoft flow.

Power Automate Variables Function

Return the value of a variable by providing its name as input parameter.

Syntax

variables('<variableName>')

Input parameters

• variableName (mandatory): The variable name of the variable whose value should be returned.

Return value

• Returns the value of the given variable.

Power Automate Variables Expression Example

variables('myVar') // if myVar = 'John'
=> 'John'

How To Use The Power Automate Variables Function

In this article, I’ll introduce you to the lastIndexOf function and will provide a step-by-step guide on how to use the function in a Microsoft flow.

Power Automate LastIndexOf Function

The function returns the index value of the last occurrence of a substring, starting from 0. The search is not case-sensitive.

Syntax

lastIndexOf('<text>', '<searchText>')

Input parameters

• text (mandatory): The string within the search string should be found.
• searchText (mandatory): The search string that should be found in the given text.

Return value

• If the searched string is found: Returns the index value for the last occurrence of a given substring, starting from its initial position.
• If the searched string is NOT found: Returns -1.
• If the searched string and text are empty: Returns 0.

Power Automate LastIndexOf Expression Example

Let’s take a look at a lastIndexOf usage example. We want to find out, at which index the ‘you’ within ‘Where are you?’ starts.

The example lastIndexOf expression would look like this:

lastIndexOf('Where are you?','You')
=> 10

To get a better understanding, why the result is ten, take a look at the following table. The first line is every character in the text. The second line is the index for each character starting at 0. You see that the ‘y’ of ‘you’ starts at 10.

Since you only appears once within the text, it is not a good example for the lastIndexOf function, since indexOf function would work here too.

The following example describes the difference between both functions.

lastIndexOf('abc abc','abc')
=> 4

indexOf('abc abc','abc')
=> 0

How To Use The Power Automate LastIndexOf Function

In case you want to create a collection that merges multiple collections without duplicates, take a look at the Power Automate union function.

In this article, I’ll introduce you to the intersection function and will provide a step-by-step guide on how to use the function in a Microsoft flow.

Power Automate Intersection Function

Return a collection of items that are present in all specified collections. The final result only includes items that appear in every collection passed to the function. If duplicates exist, the last occurrence of an item will appear in the result.

Syntax

intersection([<collection1>], [<collection2>], ...)
intersection('<collection1>', '<collection2>', ...)

Input parameters

• collection1 (mandatory): The first collection which elements should be used to create a new collection with all elements that are in all given collections.
• collection2 (mandatory): The second collection which elements should be used to create a new collection with all elements that are in all given collections.
• collection3 ..

Return value

• Returns a collection: Create a collection containing only items that are present in all the specified collections.

Power Automate Intersection Expression Example

intersection(createArray(1,2),createArray(2,3))
=> [2]

How To Use The Power Automate Intersection Function