Power Fx Cheat Sheet

Text(Today(), "[$-en-US]mmmm d, yyyy")

If(
    DateDiff(dt, Now(), TimeUnit.Minutes) < 60,
    Text(DateDiff(dt, Now(), TimeUnit.Minutes)) & " min ago",
    Text(DateDiff(dt, Now(), TimeUnit.Hours)) & " hr ago"
)

Filter(Items, Created >= DateAdd(Today(), -30, TimeUnit.Days))

// First day
Date(Year(Today()), Month(Today()), 1)

// Last day
DateAdd(Date(Year(Today()), Month(Today()) + 1, 1), -1, TimeUnit.Days)

// Approximation: subtracts 2 days per whole week. It does NOT
// account for the start/end weekday or partial weeks - good enough
// for rough estimates, not for payroll/SLA math.
With(
    {d1: startDate, d2: endDate},
    DateDiff(d1, d2, TimeUnit.Days)
    - 2 * Int(DateDiff(d1, d2, TimeUnit.Days) / 7)
)

EOMonth(Today(), 0)   // last day of current month

Text(12500, "$#,###.00")  // → "$12,500.00"

If(
    v >= 1000000, Text(v / 1000000, "#.#") & "M",
    If(v >= 1000, Text(v / 1000, "#.#") & "K", Text(v))
)

If(Len(txt) > 50, Left(txt, 47) & "...", txt)

Upper(
    Left(name, 1) &
    If(Find(" ", name) > 0, Mid(name, Find(" ", name) + 1, 1), "")
)

Proper(text)   // already lowercases the rest - no need to wrap in Lower()

AddColumns(Items, "FullName", FirstName & " " & LastName)

GroupBy(Items, "Category", "GroupItems")

Distinct(Items, Category)

SortByColumns(Items, "Priority", SortOrder.Ascending, "Date", SortOrder.Descending)

ForAll(Distinct(colItems, Title), LookUp(colItems, Title = Result))

Sequence(10)   // table 1..10 (Value column)

ForAll(
    colChanges As c,
    Patch(YourList, LookUp(YourList, ID = c.ID), { Status: c.Status })
)

// On SharePoint, StartsWith delegates - Search(), EndsWith()
// and the "in" operator do NOT (they hit the 500-2000 row cap).
Filter(Items, StartsWith(Title, searchText))

// Row counts are source-dependent:
//   Dataverse / SQL - CountRows and CountIf both delegate
//   SharePoint      - neither delegates; cache first with ClearCollect
CountRows(Filter(Items, Status = "Active"))

ClearCollect(colLocal, Filter(DataSource, Status = "Active"))

Concurrent(
    ClearCollect(col1, Source1),
    ClearCollect(col2, Source2),
    ClearCollect(col3, Source3)
)

// Non-delegable queries only return the first 500 rows
// (raise to 2000 max in Settings › General). Keep filters
// delegable, or cache the data with ClearCollect first.

Set(varResult,
    Patch(
        YourSharePointList,
        Defaults(YourSharePointList),
        {
            Title: txtTitle.Text,
            Status: {Value: "Active"},
            AssignedTo: drpAssignee.Selected
        }
    )
);
If(
    IsError(varResult),
    Notify("Save failed: " & FirstError.Message, NotificationType.Error),
    Notify("Saved successfully", NotificationType.Success);
    Navigate(scrList, ScreenTransition.None)
)

Set(varResult,
    Patch(
        YourSharePointList,
        LookUp(YourSharePointList, ID = varSelectedId),
        {
            Title: txtTitle.Text,
            Status: {Value: drpStatus.Selected.Value}
        }
    )
);
If(IsError(varResult),
    Notify("Update failed: " & FirstError.Message, NotificationType.Error),
    Notify("Updated", NotificationType.Success)
)

IfError(
    LookUp(Items, ID = varId),
    Notify("Not found", NotificationType.Error);
    Blank()
)

If(
    IsBlank(txtTitle.Text),
    Notify("Title is required", NotificationType.Warning),
    IsBlank(drpStatus.Selected),
    Notify("Select a status", NotificationType.Warning),
    // All valid - save
    Patch(YourSharePointList, Defaults(YourSharePointList),
        {Title: txtTitle.Text, Status: drpStatus.Selected}
    );
    Notify("Saved", NotificationType.Success)
)

Coalesce(varUser, "Unknown")

Navigate(scrDetail, ScreenTransition.None, {selectedItem: ThisItem})

If(!IsBlank(Param("id")), Set(varId, Value(Param("id"))))

Back(ScreenTransition.UnCoverRight)

If(App.Width < 640, "mobile", If(App.Width < 1024, "tablet", "desktop"))

If(App.Width < 640, 12, 24)

If(App.Width < 640, 1, If(App.Width < 1024, 2, 4))

Min(Parent.Width - 48, 1200)

nfColors = {
    pageBg:  ColorValue("#F9FAFB"),
    cardBg:  ColorValue("#FFFFFF"),
    primary: ColorValue("#2563EB"),
    danger:  ColorValue("#DC2626")
};

nfIsMobile = App.Width < 640;

nfCurrentUser = {
    Name:     User().FullName,
    Email:    User().Email,
    Initials: Upper(Left(User().FullName, 1))
};

Set(gblUserEmail, User().Email)

UpdateContext({ locStep: 1 })

UpdateContext({ locPanelOpen: !locPanelOpen })

With({ subtotal: qty * price }, subtotal * 1.08)

Collect(colCart, { Item: "Widget", Qty: 2 });  // add row
RemoveIf(colCart, Qty <= 0);                    // delete matching
Patch(colCart, First(colCart), { Qty: 5 });     // update row
Clear(colCart)                                  // empty

galItems.Selected.Title

Filter(
    Items,
    StartsWith(Title, txtSearch.Text),
    Status.Value = drpStatus.Selected.Value
)

If(ThisItem = galItems.Selected, nfColors.primary, nfColors.cardBg)

CountRows(galItems.AllItems) & " results"

Sum(galItems.AllItems, Amount)

NewForm(frmItem); Navigate(scrEdit);   // create mode
EditForm(frmItem); Navigate(scrEdit);  // edit mode
SubmitForm(frmItem)                     // save (button OnSelect)

// frmItem.OnSuccess
Notify("Saved", NotificationType.Success);
Back()

If(frmItem.Valid, SubmitForm(frmItem), Notify("Fix required fields", NotificationType.Warning))

Parent.Default   // in a data card control's Default

ResetForm(frmItem)

drpStatus.Selected.Value

Choices(YourList.Status)

Filter(Cities, Country.Value = drpCountry.Selected.Value)

Concat(cmbTags.SelectedItems, Value, ", ")

DefaultSelectedItems: Filter(Choices(YourList.Status), Value = "Active")

Sum(Items, Amount)     // Average(Items, Score)

Max(Items, DueDate)    // Min(Items, Price)

Round(done / total * 100, 1) & "%"

RoundUp(x, 0)   RoundDown(x, 2)   Mod(n, 2)

AddColumns(
    Items,
    "Share", Round(Amount / Sum(Items, Amount) * 100, 1)
)

Split("red,green,blue", ",")   // 1-col table (Value)

Concat(colTags, Value, ", ")

IsMatch(txtEmail.Text, Match.Email)

Match("Invoice #10432", "\d+").FullMatch   // "10432"

Set(gblData, ParseJSON(txtJson.Text));
Text(gblData.customer.name)   // coerce an untyped value

JSON(colItems, JSONFormat.IndentFour)

ColorValue("#2563EB")

ColorFade(nfColors.primary, -0.2)   // negative = darker, positive = lighter

Switch(
    ThisItem.Status.Value,
    "Active",  Color.Green,
    "Pending", Color.Orange,
    "Closed",  Color.Gray,
    Color.Black
)

RGBA(0, 0, 0, 0)

"data:image/svg+xml;utf8," & EncodeUrl(
    "<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 24 24' fill='none' stroke='#2563EB' stroke-width='2'><path d='M5 12h14M12 5v14'/></svg>"
)

// Don't concatenate the hex straight into the string - the Image can
// cache the data URI and skip re-rendering when only the color changes.
// Keep ONE base string with a token and Substitute the hex in instead.
// EncodeUrl(varHex) turns "#2563EB" into "%232563EB" so the # survives.
Substitute(
    "data:image/svg+xml;utf8," & EncodeUrl(
        "<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 24 24' fill='__COLOR__'><circle cx='12' cy='12' r='10'/></svg>"
    ),
    "__COLOR__",
    EncodeUrl(varHex)
)

// Flow ends with "Respond to a PowerApp" returning a "status" text.
Set(varOut, 'Approve Request'.Run(varSelectedId, txtComment.Text));
Notify(varOut.status, NotificationType.Success)

// Serialize a collection and hand it to the flow as one text param.
'Sync Cart'.Run(JSON(colCart, JSONFormat.IgnoreUnsupportedTypes))

// Flow returns a JSON string in "payload".
Set(varData, ParseJSON('Get Order'.Run(varId).payload));
Text(varData.customer.name);          // coerce untyped → text
Value(varData.total)                  // coerce untyped → number

ClearCollect(colLines,
    ForAll(
        ParseJSON('Get Lines'.Run(varId).lines) As line,
        { Id: Value(line.id), Name: Text(line.name) }
    )
)

If(Connection.Connected, "Online", "Offline")

// On successful load - persist to the device
SaveData(colOrders, "orders");

// App.OnStart - restore, ignoring first-run absence
LoadData(colOrders, "orders", true)   // true = don't error if missing

If(Connection.Connected,
    Patch(YourList, Defaults(YourList), varRecord),
    Collect(colPending, varRecord); SaveData(colPending, "pending")
)

// App.OnStart / a Refresh button, once Connection.Connected is true
ForAll(colPending As p, Patch(YourList, Defaults(YourList), p));
Clear(colPending); SaveData(colPending, "pending")
// SaveData is capped (~1 MB on web / limited on mobile) - keep it small.

// Timer: Duration = 30000, AutoStart = true, Repeat = true
// OnTimerEnd:
ClearCollect(colData, YourList)

// txtSearch.OnChange:
Reset(tmrDebounce)

// tmrDebounce: Duration = 400 (Repeat = false)
// OnTimerEnd:
ClearCollect(colResults, Filter(Items, StartsWith(Title, txtSearch.Text)))

// Timer: Duration = 2000, AutoStart = true
// OnTimerEnd:
Navigate(scrHome, ScreenTransition.Fade)

// tmrCountdown running; show remaining seconds
Text(RoundUp((tmrCountdown.Duration - tmrCountdown.Value) / 1000, 0)) & "s"

User().FullName      // "Jane Doe"
User().Email         // "jane@contoso.com"
User().Image         // profile photo URL

Office365Users.ManagerV2(User().Email).displayName

Office365Users.UserProfileV2("jane@contoso.com").jobTitle

Office365Users.UserPhotoV2(User().Email)   // set as an Image.Image

Office365Users.DirectReportsV2(User().Email).value   // a table

!IsBlank(LookUp(colAdmins, Email = Lower(User().Email)))

AccessibleLabel = "Delete " & ThisItem.Title

TabIndex = 1     // 0 = default order · -1 = skip (decorative only)

// On a status label - read out when its text changes
Live = Live.Polite       // Live.Assertive interrupts, use sparingly

SetFocus(txtEmail)   // e.g. after a validation error

// scrForm.OnVisible
SetFocus(txtFirstName)

// WRONG: ForAll is NOT a sequential loop - iterations don't see each
//    other's Set(), so this leaves varTotal wrong.
ForAll(colRows, Set(varTotal, varTotal + Amount))
// RIGHT: use an aggregate instead
Set(varTotal, Sum(colRows, Amount))

// WRONG: emptying with Blank() strips the columns - later Collect/Patch
//    silently loses fields or errors on the "missing" schema.
ClearCollect(colX, If(cond, SourceRows, Blank()))
// RIGHT: zero rows but keep the real schema
ClearCollect(colX, If(cond, SourceRows, Filter(SourceRows, false)))

// WRONG: Gallery.TemplateSize is evaluated once for the gallery, not
//    per row - it cannot reference ThisItem.
TemplateSize = If(ThisItem.Expanded, 120, 60)
// RIGHT: keep TemplateSize constant; vary a per-row container's Height,
//    which CAN read ThisItem.
TemplateSize = 60   // container Height: If(ThisItem.Expanded, 120, 60)

// A layout (auto-layout) container only hugs its content when its
// PARENT is also a layout container. Drop one onto a plain screen or
// inside a Rectangle and it won't self-size - nest it in another
// layout container, or give it an explicit Height/Width.

IsBlank(txtName.Text)   // a VALUE is blank / empty string
IsEmpty(colCart)        // a TABLE / collection has 0 rows

// The blue-underline "delegation" warning doesn't stop the app -
// it just caps results at the data row limit (500, up to 2000).
// You get WRONG data, not an error. Keep filters delegable or
// ClearCollect the source first, then work locally.