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.

RoleOffice365 Outlook Adobe LicenseD365 SalesD365 Marketing
Office AdminYesYes
Sales RepYesYes
Sales ManagerYesYesYes

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

Office365 Outlook
Adobe License


Office 365 Outlook
Adobe License
D365 Sales

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)

RoleOffice 365 OutlookAdobe LicenseD365 SalesD365 Marketing
Cannot filter this format

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

Office365 OutlookYes
Adobe LicenseYes
D365 SalesYes
D365 Marketing
Can be filtered this by value= Yes

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


  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.
This matrix is dynamically expanding and you can add new rows(roles) and columns(permissions) to the table

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": "$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.


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

3. Compose – Create Array Structure


4. Compose – Add Value


5. Compose – Add 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


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


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

Transpose Completed


  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!

Categories: Power Automate, SharePoint

Tags: , , ,

5 replies

  1. This is sooooooo close to what I was looking for – thanks!

    I’ve been able to recreate what you have done, but am having some trouble using a SharePoint list instead as input.

    I’ll keep hammering at it, but if you have any suggestions, they’d be more than welcome!

    Liked by 1 person

    • SP List records are in tabular format therefore overall setup should work. Can you give me more details on the issue?

      Liked by 1 person

      • That’s why I’m so happy – it certainly looks doable. Let me simplify my SharePoint list and try adapting your approach over the next couple of days. If I keep getting stuck, I will glad send you details. Thanks for a quick response and for sharing your knowledge!

        Liked by 1 person

      • Hi Kaushal, I have a couple of screenshots that show what I’m trying to do. How can I get those to you?



  1. Transpose table columns using Power Automate (With example) - 365 Community

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: