Linkedin

Exploring the improved Table function

When manipulating arrays within Canvas Apps, you’ve most likely come across the requirement to merge tables or append records to a table. Power Fx did not have a native function allowing us to achieve this. Workarounds such as ClearCollect and Ungroup got the job done, yet generally resulted in additional code complexity or redundant, temporary collections.

It’s time to say goodbye to our workarounds and welcome the improved Table() function! Within this blog post, we’ll go over the known Table functionality, quickly refresh our memory on the commonly used workarounds and more importantly explore the new Table function features.

Table of Contents

Table function - as we’ve known it

Before we dive into the new features, let’s quickly review the known functionality of the table function. The table function allows us to create a table from a list of records:

				
					Table(<Record1>, [Record2])

The output results in a table containing all columns across all records. Columns for which the record provided no value (no value or column didn’t exist) will be left blank, which can be observed in the example below.

				
					Table(
    {firstName: "John", lastName: "Doe"}, 
    {firstName: "Jane", lastName: "Doe", yearsExperience: 10}
)
firstName lastName yearsExperience
John Doe
Jane Doe 10

Before the improved functionality that we’ll explore in the ‘New table functionality’ section, it was not possible to add tables as a parameter within the Table function. In other words, we could not use this function to append / prepend records to a table nor merge tables.

Previous workarounds

In this section we’ll explore 2 common workarounds that allowed us to append records to a table or merge tables  – Ungroup and ClearCollect. As we will soon discover, these workarounds will no longer be necessary.

Ungroup

This function can be used to ungroup a nested table structure. By using some Power Fx wizardry, we could leverage this function to merge tables or append records to a table:

				
					Ungroup(
    [
        {Items: Table1},
        {Items: Table2}
    ],
    "Items"
)

In more complex cases the code would become increasingly difficult to read. My previous blog post on appending a user to a Multi-Select SharePoint Person column can be used as an example.

ClearCollect

Another approach that I commonly observed was using a collection which would store the merged table data.

				
					ClearCollect(colMerged, Table1, Table2)

As this approach could quickly result in a lot of ‘one-time’ / ‘throwaway’ collections, Ungroup was generally preferred over this approach unless the data also had to be edited locally and saved throughout the app.

New Table functionality

As of Power Apps version 3.24034.XX the table function now also accepts tables as input parameters in addition to records. This allows us to avoid the workarounds mentioned above altogether! 🥳

Appending / prepending records to table

Using a combination of record and table inputs, we can prepend or append records to a table:

				
					Table(
    {Value: "All"},
    [
        "Finance",
        "HR",
        "IT"
    ]
)

				
			
				
					Table(
    [
        {Id:1, Answer:"Apples"},
        {Id:2, Answer:"Oranges"},
        {Id:3, Answer:"Grapes"}
    ],
    {Id:4, Answer:"None of the above"}
)

A very common requirement is adding an additional choice option within a dropdown when working with SharePoint choice columns, which is showcased here.

Merging tables

In addition to adding records to a table, we can merge multiple table inputs:

				
					Table(
    [{firstName: "John"}, {firstName: "Joe"}],
    [{firstName: "Laurens"}, {firstName: "Alex"}]
)

Delegation warning?

When using datasource queries within the Table() function, you may be greeted with a delegation warning. This warning occurs since the Data Row Limit applies to all arguments of the Table function.

To better explain the impact, let’s use a scenario in which you have a SharePoint list containing 1000 records with a title that starts with ‘Network Issue’ and your Data Row Limit is set at 500. When using the table function below, your total record count would amount to 501. 

				
					Table(
    Filter(
        'Service Desk List', 
        StartsWith(Title, "Network Issue")
    ).Title,
    {Title: "Appended record 501"}
)

Although the Filter condition itself is delegable (query condition is delegated to the datasource & only returns record matching that condition), the Filter output is subject to the Data Row Limit – similar to using a delegable query as an input for a collection or variable.

You can find additional information on this topic in the following Power Apps Community Forum post. As of the time of writing, the delegation warning also seems to display for in-memory tables, for which the Data Row Limit already applies.

Common use cases

Append user to a SharePoint Multi-Select Person column

				
					Table(
    {
        DisplayName: "",
        Claims: "i:0#.f|membership|" & User().Email,
        Department: "",
        Email: User().Email,
        JobTitle: "",
        Picture: ""
    },
    Record_Reference.PersonColumn
)

This code is an improvement on my previous ungroup workaround mentioned in my ‘Appending users to a SharePoint Multi-Select Person Column’ blog post.

Add 'All' to a SharePoint choice dropdown list

				
					Table(
    {Value: "All"},
    Choices(ListName.ChoiceColumn)
)

Merging collections, tables or lists

				
					Table(
    'Student List',
    'Teacher List'
)
Scroll to Top