💡 Must-Know PowerApps Functions for Building Powerful Apps

Microsoft PowerApps enables users to build custom business apps with minimal code. One of its core strengths is the rich set of built-in functions that can handle logic, data, user input, and more. Whether you’re a beginner or an experienced app maker, these functions are essential for building flexible, user-friendly applications.

In this post, we’ve grouped the most useful PowerApps functions into categories, so you can quickly find what you need for any scenario.

🔁 Logic & Conditional Functions

These functions control decision-making within your app.

If(condition, trueResult, falseResult) – Basic IF logic.

Switch(expression, case1, result1, ..., defaultResult) – Simplifies multiple conditional paths.

IsBlank(value) – Checks if a field or variable is empty.

Coalesce(value1, value2, ...) – Returns the first non-blank value in a list.

Not(condition) – Inverts a Boolean value (true → false and vice versa).

🔤 Text Functions

Work with strings, user input, and formatted data.

Concatenate(text1, text2, ...) – Joins multiple text strings.

Text(value, format) – Formats numbers and dates as text.

Left(text, n) / Right(text, n) – Extracts characters from the start or end.

Mid(text, start, n) – Extracts a substring from the middle.

Len(text) – Returns the number of characters.

Lower(text) / Upper(text) – Converts to lowercase or uppercase.

Trim(text) – Removes extra whitespace.

Replace(text, start, count, newText) – Replaces part of a string.

🔢 Math Functions

Basic arithmetic and numerical operations.

Sum(table, column) – Totals values in a column.

Average(table, column) – Returns the average.

Round(number, decimalPlaces) – Rounds to a specific number of decimals.

Rand() – Returns a random number between 0 and 1.

Mod(number, divisor) – Gets the remainder after division.

📅 Date & Time Functions

Manipulate and format dates and times easily.

Now() – Current date and time.

Today() – Current date only.

DateValue("2025-05-11") – Converts a string to a date.

TimeValue("10:30 AM") – Converts a string to a time.

DateAdd(date, number, unit) – Adds days, months, or years.

DateDiff(start, end, unit) – Calculates the difference between two dates.

Weekday(date) – Returns a number representing the day of the week.

🔄 Collection & Data Functions

Manage and interact with collections and external data.

Collect(collection, item) – Adds a new item to a collection.

Clear(collection) – Clears all items.

ClearCollect(collection, items) – Clears and then fills a collection.

Patch(dataSource, record, changes) – Updates or creates records.

Remove(dataSource, record) – Deletes a record.

Filter(source, condition) – Returns matching records.

Sort(source, column, Ascending/Descending) – Sorts a data source.

LookUp(source, condition) – Finds the first match.

Search(table, text, columns) – Searches across specific columns.

🧠 User & App Info Functions

Get details about the user or app environment.

User() – Returns name, email, and image of the current user.

Param("paramName") – Retrieves URL parameters (useful for deep linking).

App.StartScreen – Defines the default screen when the app opens.

⚙️ Control Functions

Manage screens, variables, and user interface elements.

Navigate(screen, transition) – Changes to a different screen.

Reset(control) – Resets a control to its default state.

UpdateContext({var: value}) – Creates or updates local variables.

Set(globalVar, value) – Sets a global variable for use across screens.

Toggle(control.Visible) – Toggles visibility or Boolean values.

🧭 Wrapping Up

With these functions at your fingertips, you're well-equipped to build dynamic, responsive PowerApps solutions. Whether you're creating forms, dashboards, or data-driven apps, mastering these functions will help you bring more control and power to your apps.

🚀 Essential SPFx (SharePoint Framework) Commands for Every Developer

Whether you're new to SharePoint Framework (SPFx) or a seasoned pro, having a go-to list of core commands can significantly improve your development workflow. In this post, we'll walk through the most important SPFx CLI commands—from setting up your environment to packaging and deploying your solution.

🛠️ Setting Up Your SPFx Project

Start by scaffolding a new project using the Yeoman generator provided by Microsoft. Make sure Node.js and npm are installed before you proceed.

yo @microsoft/sharepoint

After scaffolding the project, install all dependencies:

npm install

To update dependencies later:

npm update

🔐 Trusting the Development Certificate

If you're using the local workbench, you need to trust the developer certificate for HTTPS support:

gulp trust-dev-cert

🧪 Running the Local Workbench

To build and serve your project locally, use:

gulp serve

This command starts a local server at:

https://localhost:4321/temp/workbench.html

You can also test your solution in SharePoint Online:

https://your-sharepoint-site/_layouts/15/workbench.aspx

🏗️ Building and Bundling Your Solution

During development or before packaging for production, build your solution with:

gulp build

For production-ready output:

gulp bundle --ship

📦 Packaging the Solution

Once bundled, create the .sppkg file for deployment using:

gulp package-solution --ship

The output will appear in the sharepoint/solution/ folder.

🚀 Deploying the Solution

There is no CLI command for uploading the .sppkg file—you must manually upload it to the SharePoint App Catalog via the SharePoint admin center.

If you're using an Azure CDN to host assets, you can deploy to it (assuming it's configured) with:

gulp deploy-azure-storage

🔍 Cleaning and Managing Dependencies

To clean the build output:

gulp clean

Useful npm commands for global tools:

npm install -g yo gulp

npm install -g @microsoft/generator-sharepoint

Check for outdated npm packages:

npm outdated

🧠 Final Thoughts

SPFx offers a powerful, modern way to build SharePoint customizations using familiar web development tools. Knowing these commands will help you build, test, and deploy solutions more effectively. Bookmark this list or print it out—it's a great cheat sheet for any SPFx project.

🚀 Mastering Pagination in Power Apps: Handle Large Data Efficiently!

📌 What is Pagination in Power Apps?

Pagination in Power Apps is a method used to display large datasets in smaller, manageable chunks (pages) instead of loading everything at once. This improves app performance, avoids delegation issues, and enhances the user experience.

🔹 Why is Pagination Needed in Power Apps?

Power Apps has a delegation limit when working with data sources like SharePoint, SQL, and Dataverse. By default, it can retrieve only 500 records, and the maximum limit is 2000 records.

If your dataset has thousands of records, trying to load everything at once will:

 ❌ Slow down the app

 ❌ Cause delegation warnings

 ❌ Fail to retrieve all data

✅ Solution: 

Use Pagination Instead of loading everything, fetch only a specific number of records at a time (e.g., 50 per page) and let the user navigate between pages.

Example: Employee Directory App (SharePoint List)

🔹 Suppose you are creating an Employee Directory App that fetches employee details from a SharePoint list with 5000+ records.

 🔹 Since SharePoint has delegation limitations, loading all data at once will fail or slow down the app.

 🔹 Instead, pagination allows users to see only 50 employees per page, with navigation buttons to view more.

🛠️ Expected Outcome:

 ✅ Improved loading speed

 ✅ Smooth user experience

 ✅ Efficient data handling

🔹 How to Implement Pagination in Power Apps?

We can implement pagination using Skip() and FirstN() functions.

Step 1: Define Pagination Variables

Set(CurrentPage, 1);

Set(PageSize, 50);

CurrentPage → Stores the current page number.

PageSize → Defines the number of records per page.

Step 2: Fetch Paginated Data

ClearCollect(

 PaginatedData,

 FirstN(

 Skip(EmployeeList, (CurrentPage - 1) * PageSize),

 PageSize

 )

)

Explanation:

 ✅ Skip(EmployeeList, (CurrentPage - 1) * PageSize) → Skips previous pages.

 ✅ FirstN(..., PageSize) → Retrieves only PageSize (50) records.

Step 3: Add Navigation Buttons

🔹 Next Page Button

If(

 (CurrentPage * PageSize) < CountRows(EmployeeList),

 Set(CurrentPage, CurrentPage + 1)

)

🔹 Previous Page Button

If(

 CurrentPage > 1,

 Set(CurrentPage, CurrentPage - 1)

)

📌 Functionality:

 ✅ Next Page: Increases CurrentPage by 1 if more records are available.

 ✅ Previous Page: Decreases CurrentPage by 1 if not on the first page.

🔹 Alternative Approaches for Pagination

🔹 Lazy Loading (Load More Button)

Instead of pages, fetch additional data when the user clicks "Load More".

Efficient for infinite scrolling apps.

🔹 Server-Side Filtering

Instead of fetching all data, apply filters before retrieving data.

Example: Fetch only employees from a specific department.

How to List All SharePoint Lists with PowerShell Using PnP

When managing SharePoint Online, administrators often need to retrieve a list of all libraries and lists in a site. With the PowerShell PnP module, this process is quick and efficient. In this post, we’ll walk through a simple script that connects to a SharePoint site and retrieves the title and description of all lists.

Prerequisites

To follow along, make sure you have the PnP PowerShell module installed. You can install it using:

Install-Module -Name PnP.PowerShell

Script Overview

Here's the script that connects to your SharePoint Online site and displays the title and description of each list:

# Connect to SharePoint site

Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -Interactive

# Get all lists

$lists = Get-PnPList

# Select and display Title and Description

$lists | Select-Object Title, Description | Format-Table -AutoSize

Use Cases

• Auditing site contents

• Documenting SharePoint structure

• Identifying unused or unnamed lists

PowerShell Script to Bulk Update "Next Review Date" for PDFs in SharePoint Online

  In document management scenarios, it's common to have review schedules for files such as policies, SOPs, or manuals. This blog post walks you through a PowerShell script that automates the process of updating the “Next Review Date” field for all PDF documents in a SharePoint Online document library.

We’ll use the PnP PowerShell module to fetch and update items efficiently, while also handling time zone specifics like CST/CDT.

Prerequisites

Before running the script, ensure:

• You have the PnP PowerShell module installed:
  Install-Module PnP.PowerShell -Scope CurrentUser

• You have the correct Site Collection URL and Library Internal Field Name.

• Your account has permission to modify list items in the target document library.

What the Script Does

1. Connects to SharePoint Online using PnP PowerShell.

2. Filters all PDF documents in the specified library.

3. Calculates a future "Next Review Date" (e.g., 9999-09-09) adjusted for CST or CDT.

4. Updates the metadata field (Next Review Date) for each filtered document.

PowerShell Script

$SiteUrl = "[SiteCollection]"

Connect-PnPOnline -Url $SiteUrl -UseWebLogin 

# Define the Document Library and the field name for Next Review Date

$libraryName = "[LibraryName]"  # Adjust the library name accordingly

$reviewDateField = "Next_x0020_Review_x0020_Date"  # Internal name of the field

# Get all items from the document library, including FileLeafRef

$documents = Get-PnPListItem -List $libraryName -Fields "FileLeafRef"

# Filter for PDF files only

$filteredDocuments = $documents | Where-Object { $_["FileLeafRef"] -like "*.pdf" }

# Set the new review date to a far future date

$newReviewDate = [datetime]"9999-09-09"

# Determine if the date falls under CDT or CST

$centralTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById("Central Standard Time")

$isDST = $centralTimeZone.IsDaylightSavingTime($newReviewDate)

# Adjust for time difference from UTC to IST

if ($isDST) {

    $newReviewDate = $newReviewDate.AddHours(10).AddMinutes(30)

} else {

    $newReviewDate = $newReviewDate.AddHours(11).AddMinutes(30)

}

# Loop through filtered documents and update the field

foreach ($document in $filteredDocuments) {

    $fileLeafRef = $document["FileLeafRef"]

    Write-Host "$libraryName Document FileLeafRef: $fileLeafRef"

    Set-PnPListItem -List $libraryName -Identity $document.Id -Values @{ $reviewDateField = $newReviewDate }

}

Notes

• Next_x0020_Review_x0020_Date is the internal name for the “Next Review Date” field. You can find this in list settings or by inspecting the field using PowerShell or the browser.
• The script uses a placeholder date 9999-09-09 which might be used for archiving or indefinite review.
• Adjusting time based on DST (Daylight Saving Time) ensures that timestamps align correctly across regions.

Use Cases

• Automating document review schedules.
• Archiving outdated or permanent documents.
• Preparing metadata for records management.

Convert IST to CST/CDT in PowerShell – Handling Daylight Saving Time (DST)

Working across time zones can be tricky, especially when Daylight Saving Time (DST) is involved. If you’re dealing with automation, scheduling, or data transformation tasks, converting between Indian Standard Time (IST) and Central Time (CST/CDT) becomes essential.

In this post, I’ll walk you through a PowerShell function that smartly handles the conversion of an IST datetime to either CST (Central Standard Time) or CDT (Central Daylight Time), depending on the date and DST rules.

The PowerShell Function

Below is a reusable PowerShell function that converts IST to CST/CDT accurately by accounting for Daylight Saving Time.

function Convert-ISTToCSTorDST {

    param (

        [datetime]$ISTDate    # Input IST DateTime to be converted

    )

    # Central Time Zone Information

    $centralTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById("Central Standard Time")

    

    # Check if the given ISTDate is in Daylight Saving Time (CDT) or Standard Time (CST)

    $isDST = $centralTimeZone.IsDaylightSavingTime($ISTDate)

    # Convert IST to UTC (since IST is UTC +5:30)

    $utcDate = $ISTDate.AddHours(-5).AddMinutes(-30)

    # Convert to CST or CDT

    if ($isDST) {

        # If in Daylight Saving Time (CDT), subtract 5 hours from UTC to get CDT

        $convertedDate = $utcDate.AddHours(-5)

        Write-Host "Converted Date (CDT): $convertedDate"

    } else {

        # If in Standard Time (CST), subtract 6 hours from UTC to get CST

        $convertedDate = $utcDate.AddHours(-6)

        Write-Host "Converted Date (CST): $convertedDate"

    }

    return $convertedDate

}

Example Usage

$ISTDate = Get-Date "2025-03-15 10:00:00" # Example IST datetime

$convertedDate = Convert-ISTToCSTorDST -ISTDate $ISTDate

How It Works

1. Finds the Central Time zone using system identifiers.

2. Determines if DST is in effect using .IsDaylightSavingTime().

3. Converts IST to UTC by subtracting 5 hours and 30 minutes.

4. Adjusts from UTC to CST or CDT by subtracting either 6 or 5 more hours.

Bulk Import Excel Data to SharePoint List Using PowerShell and PnP

 

Managing large datasets in SharePoint can be tricky, especially when you're dealing with Excel files and need to avoid list view threshold issues. In this guide, I’ll walk you through a PowerShell script that efficiently imports data from Excel into a SharePoint Online list using PnP PowerShell — with batching support for performance.

Prerequisites

Make sure you have the following before running the script:

• SharePoint Online site URL

• Excel file with data properly formatted

• PnP PowerShell module installed (Install-Module PnP.PowerShell)

• Appropriate SharePoint permissions

What the Script Does

• Connects to your SharePoint site

• Loads and reads an Excel file

• Converts Excel date values

• Batches records in groups (to avoid the 5000 item threshold)

• Adds the items to your SharePoint list or library

• Logs execution time

PowerShell Script

$siteUrl = "[Site Collection URL]"

Connect-PnPOnline -Url $siteUrl -UseWebLogin

# Capture the start time

$startTime = Get-Date

# Define lists and field mappings

$LibraryName = "[LibraryName]"

$excelFilePath = "[excelFilePathwithExtention]" 

# Import data from the Excel file using Import-Excel

$excelData = Import-Excel -Path $excelFilePath

# Define the batch size (to avoid SharePoint's list view threshold of 5000 items)

$batchSize = 200

# Initialize a list to hold batches of items

$batch = @()

# Loop through each row in the Excel data

foreach ($row in $excelData) {

    # Calculate the OBSERVATION_DATE

    $baseDate = [datetime]"1900-01-01"

    $convertedDate = $baseDate.AddDays($row.OBSERVATION_DATE - 2)

    # Map Excel data to SharePoint fields

    $Values = @{

        "Title"                     = $row.Title

        "SHIFT"                     = $row.SHIFT

        "OBSERVATION_DATE"         = $convertedDate

        "BEHAVIOR"                 = $row.BEHAVIOR

        "ROLE"                      = $row.ROLE

        "Behaviour_unsafeCondition" = if ($row.AT_RISK_BY_UNSAFE_COND -eq "N") { "No" } 

                                      elseif ($row.AT_RISK_BY_UNSAFE_COND -eq "Y") { "Yes" } 

                                      else { "" }

        "LOCATION_ID"               = $row.LOCATION_ID

        "AREA_ID"                   = $row.AREA_ID

        "SUB_AREA_ID"               = $row.SUB_AREA_ID

        "LINE_ID"                   = if ($null -eq $row.LINE_ID -or $row.LINE_ID -eq "") { $null } else { $row.LINE_ID }

        "CATEGORY_ID"               = $row.CATEGORY_ID

        "SUB_CATEGORY_ID"           = $row.SUB_CATEGORY_ID

    }

    $batch += $Values

    # Process the batch if it reaches the threshold

    if ($batch.Count -ge $batchSize) {

        foreach ($item in $batch) {

            Add-PnPListItem -List $LibraryName -Values $item -Verbose

        }

        Write-Host "Added $($batch.Count) items to $LibraryName"

        $batch.Clear()

    }

}

# Add remaining items if any

if ($batch.Count -gt 0) {

    foreach ($item in $batch) {

        Add-PnPListItem -List $LibraryName -Values $item -Verbose

    }

    Write-Host "Added the last batch of $($batch.Count) items to $LibraryName"

}

# Calculate execution time

$endTime = Get-Date

$executionTime = $endTime - $startTime

Write-Host "Script executed in:" $executionTime

Key Benefits

• Efficient batching: Avoids the 5000-item limit by processing in chunks.

• Dynamic date conversion: Transforms Excel numeric dates into valid DateTime objects.

• Safe and robust: Skips null values and handles conditional logic cleanly.