Transpose table columns using Power Automate (With example)

Power automate always works with defined column headers.

Let’s take an example. Contoso uses a permission matrix to determine access provisioning based on employee role.

HR manager wants to create role based permissions checklist for new employees.

We need to get all columns that have “Yes” for a specific role. The final result should be something like this.

Office Admin

CTO

We cannot dynamically filter columns in a table using flow. Filtering requires items to be in array format. Therefore, each column should be converted to an array item.

In other words, we need to transpose specific row and create a tabular structure in order to filter by column value

i.e. Original row ( Cannot be filtered)

Transposed row (Can be filtered) : This has Name and Value headers

So how do we do this in Power Automate? Let’s start the design.

A. GET SPECIFIC ROW

1. Create excel file with the permission matrix. You may want to use Sharepoint for this however, there is a limit for maximum number of columns you can have in a SharePoint list (50). Excel on the other hand supports ~16000 columns.

Important – Column headers should not contain any special characters other than dash (-)

2. Save the file in SharePoint.

3. Create a new flow – for this example, I will use an instant flow with two inputs (Employee Name and Role)

4. Get relevant row from the permission matrix using ‘Get a Row’ action

Sample output body:

    "body": {
        "@odata.context": "https://excelonline-ce.azconn-ce.p.azurewebsites.net/$metadata#drives('b%)/items/$entity",
        "@odata.etag": "",
        "ItemInternalId": "CTO",
        "Role": "CTO",
        "Office 365 Outlook": "Yes",
        "Adobe License": "Yes",
        "D365 Sales": "Yes",
        "D365 Marketing": ""
    }

All fields are key value pairs inside a single object and we cannot filter these values.

B. TRANSPOSE COLUMNS

Follow below steps in the exact order. I am using a series of compose actions for this.

1. Assign body to a string variable (Var_RowString)

1. Compose – Remove Invalid Characters : Get row returns url data and “://” part could cause issues during conversion. We can remove that using replace function

replace(variables('Var_RowString'),'://','')

3. Compose – Create Array Structure

replace(outputs('Remove_Invalid_Characters'),',','},{')

4. Compose – Add Value

replace(outputs('Create_Array_Structure'),':',',"Value":')

5. Compose – Add Name

replace(outputs('Add_Value'),'{','{"Name":')

6. Compose – Create Permissions Object

In this step, we are manually creating a JSON object that has the permissions array.

Use the output from previous step for this.

Output object will have escape characters (“/”). Reason for this is that flow considers this value as a string instead of an array. To avoid this, we need to convert this object using JSON function

7. Compose – Convert to JSON Object

json(outputs('Create_Permissions_Object')['permissions'])

8. All steps are outlined below:

Final result with columns as array items

9. Before filtering, one last step is to parse this object and get Name/Value pairs

Parse JSON

Schema:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Name": {
                "type": "string"
            },
            "Value": {
                "type": "string"
            }
        },
        "required": [
            "Name",
            "Value"
        ]
    }
}

Transpose Completed

C. FILTER COLUMN VALUES

1. Use “Filter Array” step. From value should be the output body of “Parse JSON” action
2. All we need now is to filter all columns where Value = Yes

2. Create HTML Table (permission summary)

You can use the body from “Filter array” as the from value. This will list out filtered values in a HTML table

3. Send Notification Email

Go to HTML view of the email and insert the HTML table output.

4. All done! Lets test this..

Note : You can now further extend the flow to perform row based actions for each column.

If you need to create an employee level checklist, all you have to do is write the same HTML table data into a SharePoint list that has Employee Name, Permission Name, Provisioned(Yes/No) columns.

You can use this method to transpose any object with similar structure. There could be a few tweaks however, the overall logic should work.

Happy Learning!

Original Post https://immersivecoder.com/2021/08/29/transpose-table-columns-using-power-automate-with-example/