Dyota's blog

Power Apps Cookbook

Text formulas

Text truncation

This will truncate a text that's too long and abridge it by an ellipsis at the end.

With(
    {
        Text: "", // input text
        Cutoff: 20 // input number
    },
    Left(Text, Cutoff) & If(Len(Text) > Cutoff, "...", "")
)

Table formulas

Table of alphabet characters

Clear(colLetterArray);
With(
    {Alphabet: Sequence((91-65), 65, 1)}, 
    ForAll(
        Alphabet,
        With(
            {FirstLetter: Char(Value)},
            ForAll(
                Alphabet,
                Collect(
                    colLetterArray,
                    FirstLetter & Char(Value)
                )
            )
        )
    )
)

Transform column "in place"

In the example below, we do a Substitute() on the column Description by firstly making a temporary column in memory called Description_temp.

RenameColumns(
    DropColumns(
        AddColumns(
            Reports_Catalogue,
            "Description_temp",
            Substitute(Description, "_x000D_", "<br>")
        ),
        "Description"
    ),
    "Description_temp",
    "Description"
)

When you put a horizontal Gallery into a vertical Gallery (or vice versa), I call it a matrix. This is nice to displaying objects in a "tiled" manner.

Note that you can't go any deeper than one level of nested Galleries, so this is as good as it gets, so whatever is in your inner gallery has to be the information that you really mean to display.

Items
With(
    {
        Columns: 5, // input number of columns (left-right)
        Collection: colCollection // input collection 
    },
    With(
        {
            Rows: RoundUp(CountRows(Collection)/Columns, 0)
        },
        AddColumns(
            Sequence(Rows, 1, Columns),
            "RowGallery", 
            LastN(
                FirstN(Collection, Value + Columns - 1),
                Columns
            )
        )
    )
)
Items
ThisItem.RowGallery

AD Tasks

Get all names in company

This formula uses the alphabet table colLetterArray as described above.

Clear(colPeople);

ForAll(
    colLetterArray,
    Collect(
        colPeople,
        Office365Users.SearchUserV2({
            searchTerm: colLetterArray[@Value],
            top: 2000
        }).value
    )
)

Sorting tasks

Word list based on frequency

The below example takes a collection colReportCatalogue, takes all of the texts contained within the column Description, concatenates it all together into one big corpus, and sorts individual words (spaces being the word boundary) by how frequently they occur.

ClearCollect(
    colWordList,
    Sort(
        Filter(
            GroupBy(
                AddColumns(
                    Split(
                        Concat(
                            colReportsCatalogue,
                            Title & " " & PlainText( Substitute(Description, "<br>", " ") ) & " "
                        ),
                        " "
                    ),
                    "word",
                    Lower(Trim( Substitute(Substitute(Result, ".", ""), ",", "") ))
                ),
                "word",
                "group"
            ),  
            Not(
                word in ["the", "of", "a", "report", "This", "to", "and", "provides", "for", "in", "with", "that", "all", "are", "be", "on", "ensure", "&", "an", "not", "have", "where", "from", "within", "would", "which", "by", "its", "than", "then", "their", "it", "at", "is", " "]
            )
            && Len(word) <> 0
        ),
        CountRows(group),
        Descending
    )
)

Layout tasks

Aligning

For centre align: X: (Parent.Width - Self.Width)/2 Y: (Parent.Height - Self.Height)/2

For bottom right align: X:

With(
    {Offset: 20},
    Parent.TemplateWidth - Self.Width - Offset
)

Y:

With(
    {Offset: 10},
    Parent.TemplateHeight - Self.Height - Offset
)

For top right align: X:

With(
    {Offset: 20},
    Parent.TemplateWidth - Self.Width - Offset
)

Y:

With(
    {Offset: 10},
    Offset
)

#powerapps