Unpivot a table to display columns across rows

[Amik]

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.

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:

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:

Filter(
    ColUnPivot,
    Value = "Due"
)