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
CTOYesYesYes

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

CTO

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
CTOYesYesYes
Cannot filter this format

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

NameValue
RoleCTO
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.

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



Categories: Power Automate, SharePoint

Tags: , , ,

1 reply

Trackbacks

  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:

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

%d bloggers like this: