Power Community

Power Community

Unpivot a table to display columns across rows

Scenario

I recently came across the following scenario posted on the forums and I moved my response to this blog.

We have the following table structure.

Amik_4-1711405114659.png

We want to display each Name and Category in a Gallery, but display only Categories which are “Due”.

One way to achieve this is to unpivot the table structure by rotating the columns across rows and the rows across columns. Each category and name will need to be duplicated per the number of unique names:

Amik_1-1711403795218.png

Unpivot the table

1. For this tutorial, create the following Collection by applying the below to the OnSelect property of a Button control. This will act as the data source we want to unpivot:

ClearCollect(
    MyTableToPivot,
    {
        Name: "Imran Khan",
        Category1: "Not Due",
        Category2: "Due",
        Category3: "Not Due"
    },
    {
        Name: "Bruce Lee",
        Category1: "Not Due",
        Category2: "Not Due",
        Category3: "Due"
    },
    {
        Name: "Susan Storm",
        Category1: "Due",
        Category2: "Not Due",
        Category3: "Due"
    },
    {
        Name: "John Smith",
        Category1: "Not Due",
        Category2: "Not Due",
        Category3: "Not Due"
    }
)

2. Create another Button control and add the following onto the OnSelect property of the Button:

ClearCollect(
    colSourceColumns,
    [
        "Category1",
        "Category2",
        "Category3"
    ]
);
Clear(ColUnPivot);
ForAll(
    MyTableToPivot,
    ForAll(
        colSourceColumns,
        Collect(
            ColUnPivot,
            If(
                Value = "Category1",
                {
                    Name: Name,
                    Category: "Category1",
                    Value: Category1
                },
                Value = "Category2",
                {
                    Name: Name,
                    Category: "Category2",
                    Value: Category2
                },
                Value = "Category3",
                {
                    Name: Name,
                    Category: "Category3",
                    Value: Category3
                }
            )
        )
    )
)

The Collection “ColUnPivot” will return a table with the structure that we need.

3. Create a Gallery control and in the Items property, enter:

ColUnPivot

Filtering the table

However, in our Gallery, we only want to display all Names and Categories which are marked as “Due”.

To achieve this, we can take our transformed table, and “Group” each distinct Name to create a separate table using the GroupBy function. For example, if we apply the below to the Items property of a Gallery:

GroupBy(
    AddColumns(
        ColUnPivot,
        "GroupedByField",//duplicate the field we grouped by
        Name
    ),
    "Name",
    "GroupedItems"
)

Amik_3-1711404235929.png

We can see this formula returns four tables (per the number of distinct names).

We can then leverage the ForAll function to Filter each table where the Category field equals “Due”, and then Ungroup results.

To illustrate, and as a final step, apply the below into the Items property of the Gallery:

Ungroup(
    //for each grouped table
    ForAll(
        GroupBy(
            AddColumns(
                ColUnPivot,
                "GroupedByField", //duplicate the field we grouped by
                Name
            ),
            "Name",
            "GroupedItems"
        ),
        Filter(
            GroupedItems,
            Value = "Due"
        )
    ),
    "Value"
)

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Unpacking the VMware Rapid Migration Plan

Allie Wolf Allie is the Marketing Manager at Dynamic Consultants Group. She graduated from Northwest Missouri State University with...

More Articles Like This

- Advertisement -spot_img