Efficiently Count Non-Blank and Blank Rows in Power Apps using IsBlank

Introduction: In the world of data management, it’s often essential to quickly evaluate and count non-blank records. For those using Microsoft’s Power Apps, there’s a specific function designed for this exact purpose: CountA. This blog will provide an in-depth look at how to effectively count non-blank rows with Power Apps and explore the capabilities of the lesser-known but crucial function, IsBlank.

Getting Acquainted with CountA in PowerFX

Before delving into practical applications, it’s vital to understand the theoretical aspects of PowerFX’s count-related functions. Power Apps, through its PowerFX formula language, offers a variety of functions, but deciphering their application from official documentation can be challenging. This post aims to simplify these complexities by offering real-world examples and additional context.

Our Data Structure Scenario

Consider a typical parent-child relationship: there’s a parent table defining property types and a child table detailing properties. These two are interlinked using a ‘PropertyTypeID’.

The objective? We aim to count records where the TaxBand value is either blank or not, based on this relational data.

Setting Up the Example App

In our hypothetical app, we employ a screen populated with nested galleries to illustrate how to count records with varying tax band statuses (empty or filled) based on the property type ID.

Configuration details:

• Parent gallery (galParent) Item property: PropertyTypes
• Nested child gallery (galChild) Item property: Filter(Properties, ThisItem.PropertyType_ID=PropertyTypeID)

Counting Non-Blank Rows by Group

With Power Apps, it’s straightforward to display a count of records that aren’t blank. Simply insert a label into the parent gallery’s item template and set its text property as follows: CountA(galChild.AllItems.TaxBand). To generalize for the entire ‘Properties’ data source, use CountA(Properties.TaxBand). An alternative delegable formula for Dataverse is CountIf(Properties.TaxBand, !IsBlank(TaxBand)).

Counting Blank Rows by Group

In the absence of an inverse function to CountA, counting blank rows becomes a tad bit more complex. The solution? Use the CountIf function paired with a condition to exclusively count blank rows: CountIf(galChild.AllItems, IsBlank(TaxBand)).

Navigating Delegation Concerns

When working with large data sources, delegation issues with count-related functions can arise, leading to inaccurate results, especially with platforms like SharePoint. Fortunately, Dataverse users can enable delegation for CountRows and CountIf by activating the ‘Enhanced delegation for Microsoft Dataverse’ option. As an added bonus, Power Apps users can look forward to delegation support for UpdateIf in late 2023. This accentuates the advantages of Dataverse as a preferred data source.

Counting non-blank and blank rows in grouped data can be effortlessly achieved with Power Apps, especially when harnessing the power of functions like CountA and IsBlank. These tools, when utilized effectively, can enhance data analysis and management, streamlining tasks and ensuring accuracy. For those seeking further assistance or more in-depth technical support, please contact us. We’re here to help and guide you through every step of your Power Apps journey.