How to transpose Excel table via flow?

Have you ever build Power Automate (Flow) for transposing Excel table?. I came across such problem and designed a flow.  This flow transpose your excel table data.

I have one excel sheet- Sales Data inside SalesTable. It has three columns- 1. CustomerID 2. Category 3. Sales

I want to transpose above table into following format-

Steps-

• I am using flow trigger “manually trigger a flow”. you can use your flow trigger as per your requirement.
• First step is to create unique CustomerID  array. To do that use select action and map CustomerID from “List rows present in a table-Sales Data” then use union() function in a compose which will give all unique CustomerID in an array.

union(body(‘Select-getting_customer_ID_in_a_collection’),body(‘Select-getting_customer_ID_in_a_collection’))

• Then initialise four variables- VarTotal, VarA, VarB & VarC.
• One important point is that you need to initialise as many variables as many your categories. Here i have three categories- A,B & C

• Now we will iterate each CustomerID through Apply to each Action. And inside apply to each i have used one compose which will give us current loop item. This will be used in filter query of “List rows present in a table”.

• After this we need to check whether list item is already exist in Transpose Table. For this we will use length() to count number of items for each CustomerID. If Count is zero then we will add that CustomerID into Transpose table using “Add a row” action.

length(outputs(‘List_rows_present_in_a_table’)?[‘body/value’])

• As we have multiple eateries for category column so we need to collect Unique Categories in an Array. Again we will use union() function for the same.

union(body(‘Select-collecting_all_categories’),body(‘Select-collecting_all_categories’))

• Now for each categories we will get all rows from Sales Data table.

• Then we will filter rows based on CustomerID. This will be useful for making total of sales values in VarTotal. I have used Increment variable action here.
• So we have now total sales data into variable VarTotal but it needs to be collected in separate category variables-VarA,VarB & VarC . For this we will use condition action which will compare category and then set each Variable value to varTotal value.

• Now we will update each row in Transpose Table by using “Update a Row” action. Here we will pass customerID and each category variable output.

• Finally we will reset VarTotal Value as zero so that it can be used repeatedly inside  apply to each for each category.

Complete Flow Design-

For more Power Automate (Flow) post click here.

Thank You!!!

-Gopenly