In this #PowerShot, I will show you how to delete empty rows or rows with empty key columns in an MS Excel table using Power Automate and a Win Automation process.
Let's Get Started!
The current excel online connector in Power Automate does not provide with the capability to detect and delete empty rows or rows with a desired key column value as null. To overcome this limitation, we can use Win Automation processes to read the excel file and get rid of the empty items.
Win Automation
Step 1: We will get the path of the temporary Excel file from the main flow (in Power Automate) via command line arguments. Therefore, the first step in the process is to Get the Command Line Arguments.
Step 2: Once the file path is retrieved (note that the command line arguments return a list of inputs. We need the first one only as the file path is being passed from the flow), we will add an action to Launch excel and open the file.
Step 3: After the excel instance is open and the context is set, we need to get the first free column from the excel spreadsheet. This is to get the number of columns in the sheet.
Step 4: Set variable action to set a null value that will be used to compare the excel cell for null values.
Step 5: Set variable action to set the row index that needs to be deleted based on the detected null value.
Step 6: After retrieving the number of columns, we will add a step to get the actual number of columns that is by subtracting one from the outputs of step 3.
Step 7: After retrieving the number of rows, we will add a step to get the actual number of rows that is by subtracting one from the outputs of step 3.
Step 8: Once the column and row numbers are retrieved, we need to read the data from the table and identify the column number on which we want to check null values.
Step 9: For each loop to iterate over each row read from the spreadsheet.
//Loop starts
Step 10: Increase variable action to determine the row number of the current iteration.
Step 11: If loop to check if the key column in the excel sheet is empty. This comparison is done by comparing the key column value with the empty variable initialized in the beginning of the process.
//If loop starts
Step 12: If the condition is met, delete the current row from the spreadsheet.
Step 13: After deleting the current row, reduce the row index to appropriately match for the next row.
//If loop ends
//For each loop ends
Step 14: Save Excel to save the excel instance with the changes made in the flow.
Step 15: Close Excel instance so that the file can be sent back to Power Automate.
Power Automate
We will create a UI Flow to call the Win Automation process and then invoke the UI flow in a main flow.
UI Flow
Step 1: Create a UI Flow for a Desktop app and configure a simple text input. This input will contain the path of the temporarily created Excel files that will be used to pass through the Win Automation process to delete the rows with null values.
Step 2: Add the Run Win Automation action and provide the path of the process. In the Command Line Arguments property of this action, directly pass the inputs. Save and close the UI flow.
Flow
Step 1: Trigger- For a selected file: to run the flow from a selected file in the SharePoint document library.
Step 2: Action- Get file properties: to get the file properties of the file on which the process needs to be invoked.
Step 3: Action- Get file content: to get the file content of the file from the SharePoint document library. This will be used to create the excel file on desktop on which the Win Automation process will run.
Step 4: Action- Create file: to create the temporary file for the Win Automation process to run.
Step 5: Action- Run a UI flow for Desktop: to invoke the Win Automation process via the UI flow created in the previous sections and pass the file path on which the action needs to be performed.
Step 6: Action- Delete file- to delete the current file from the SharePoint document library and create a new one in its place with all the empty data deleted.
Step 7: Action- Get file content using path: to get the file content of the modified file. This will be used to create the new file in the SharePoint document library.
Step 8: Action- Create file: to create a new file in the SharePoint document library with all the changes.
Setup in Action
In this post, we saw how to implement a process with Win Automation and invoke it in Power Automate to delete empty rows (or rows with an empty key column) in excel.
I hope you found this interesting and it helped you. Thank you for reading!
Comments