Power FX - Coding Complex Formulas

There’s a Chinese proverb that says “A journey of a thousand miles begins with a single step” and that equally applies to coding complex formulas in Power FX

I was asked about a previous post Filter Based on Child Data, and whether it is possible to get the same outcome, but without using ForAll and therefore have all the code in a single Power FX statement

The answer is yes it can be done, but the single statement is quite complex and uses 6 Power FX functions, ClearCollect, Sort, Distinct, ShowColumns, AddColumns and Filter. To code a formula of this complexity in one go is hard, way too hard! A much better way is to build the formula step by step from the inside out

Filter based on Child Data

To recap my user case, I have a simple Dataverse structure of two tables. The parent table is called Programme and the child table is Project. A programme can contain multiple projects

Take it Step by Step

I want to create a sorted list of the programmes that contain at least one project that has a ‘Project Status’ of ‘Red’, so lets take this step by step

Step 1

First, create a Collection of rows from the Project table where the ‘Project Status’ column has a value of “Red” using the Filter function

ClearCollect(
   colProgrammes,
   Filter(
      Projects,
      'Project Status' = "Red"
   )
);

Step 2

The key item of information that I need is the name of the project’s parent programme, so I’ll add this to the collection by wrapping the Filter function inside an AddColumns function. I can get the name of the parent programme using dot.notation

ClearCollect(
   colProgrammes,
   AddColumns(
      Filter(
      Projects,
      'Project Status' = "Red"
      ),
   "ProgrammeName",
   'Parent Programme'.Name
   )
);

Step 3

As I only need a list of the Programme names and no other information, I’ll remove all the other columns by applying the ShowColumns function so the only item in the collection is the one column I want

ClearCollect(
   colProgrammes,
   ShowColumns(
      AddColumns(
         Filter(
         Projects,
         'Project Status' = "Red"
         ),
      "ProgrammeName",
      'Parent Programme'.Name
      ),
   "ProgrammeName"
   )
);

Step 4

As a Programme can have multiple projects and more than one project could have a status of ‘Red’, the same programme may appear twice in the list. To remove any duplicates use the Distinct function

ClearCollect(
   colProgrammes,
   Distinct(
      ShowColumns(
         AddColumns(
            Filter(
            Projects,
            'Project Status' = "Red"
            ),
         "ProgrammeName",
         'Parent Programme'.Name
         ),
      "ProgrammeName"
      ),
   ProgrammeName
   )
);

Step 5

Now I have my list, I want to sort the collection in alphabetical order. To sort a single column collection I reference it using ‘Value’

ClearCollect(
   colProgrammes,
   Sort(
      Distinct(
         ShowColumns(
             AddColumns(
                Filter(
                Projects,
                'Project Status' = "Red"
                ),
             "ProgrammeName",
             'Parent Programme'.Name
             ),
         "ProgrammeName"
         ),
      ProgrammeName
      ),
   Value,
   SortOrder.Ascending
   )
);

And I now have the list I need

Summary

Power FX formula are calculated from the inside out, so a good rule is to code in the same way. Before starting coding, its well worth taking time to think through the best approach. Here I have started with the core data (in this case the filtering of the Projects table), and then gradually refined the content till I have the data I require

Happy (low) coding!

Hiredgun.tech: Dataverse Navigation. Identifying Rlated Child, Parent and Grandparent Rows
Hiredgun.tech: Dataverse Relations: Identifying Children, Grandchildren & Great-GrandChildren
Power Apps Reference: AddColumns and ShowColumns

Power FX – Coding Complex Formulas