Bring DML to Power BI with Power Automate – Delete Records
An exciting addition to Power BI April release is Power Automate Visual. This adds a host of capabilities to Power BI directly without relying on Power Apps. Users can now activate PA flows directly at the click of a button from Power BI passing necessary action values. This is not only restricted to operations like Send Mail or Create tasks but could also bring the Powerful Data Manipulation capabilities within Power BI. Here we take up a simple example to show how users can now delete records from Database with a click from Power BI :
Start with adding Power Automate Visual from AppSource
Here we have a Skills table imported from SQL Server displayed on pbix. Now we add the PA visual
and map the field SkillID to the PA visual as we want that ID to be passed for deletion based on filtering on Power BI. Once mapped we click Edit on the PA visual and proceed :
Click New àInstant cloud flow . This takes to the flow editor within Power BI Desktop. Here select New Step and search for SQL Server , then add Delete Row
Here Configure the Delete Row to connect to the on-premise SQL Server we have populated data in power BI from and the table that we want to update. This would use the Gateway connection for an On-premise SQL Server in our case. The RowID field would prompt with the SkillID field that we have passed from Power BI , we need to select the same and the flow would then look like below :
Click Save and Apply and move back to the report:
Now format the Power Automate Visual to display as a button by resize and button text properties.
This is now ready for action :
Use filters to select the row that we want to delete. Eg. We select Skill ID 3 and click the Delete User which triggers the flow :
Since ours is an import model, we can now refresh the report to see the record deleted. You may verify it from the SQL Table too.