Power Automate find and replace

[syasmin25]

I am relatively new to power automate since I have not had the opportunity to use it to its full potential yet, so I was looking for some guidance. I am trying to use the find and replace function in Power Automate to look up a specific column in an Excel sheet that is stored on the desktop and replace it with that of another column by looking it up in SQL. Are there any tutorials about something similar to this?  I tried to do it in a couple of different ways with the last one being setting up the Excel file as a variable, and keep on getting different types of errors. 
Any suggestions would be highly appreciated. 

[trice602]

Hi @syasmin25 ,

Here's a good framework for you to start with, essentially you are going to run a SQL query and then for each item in your Excel table (that matches a key column key value), you are going to update the row with your dynamic content from your query.

You can start with a manual trigger and get this up and running first, then update it to automated if needed.  But essentially you have three actions: Query, For Each, Update row.

[Agnius]

@trice602 's solution would work in cloud flows. But if I read your description correctly, I think you are looking for a solution using desktop flows.

As to your question, there are plenty of resources online that are very easy to find. A quick Google search returned this on using the Find and replace in Excel worksheet: https://www.serverlessnotes.com/docs/find-and-replace-in-excel-using-power-automate-desktop

You can likely also find more articles and videos. You probably shouldn't expect to find one that does exactly everything you need, but you can easily find tutorials on the separate parts of your flow - namely using Excel and using SQL.

If you want us to suggest the steps, we would need more details on what you are trying to do exactly, with sample data. Otherwise, you could share screenshots of your flow and describe what exactly it is that doesn't work and then we can come up with suggestions on how to fix that.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[syasmin25]

Hello, 

Thank you so much for the article. My find and replace would more or less do the work of v-lookup from the two sources. I have taken both of your advice and started from small steps. I was able to run the SQL script and have attached a screenshot of my steps in the image below. My issue currently is being able to store all the data extracted from the SQL server. Currently, with the steps that I am using, it is only saving either the first column or the first row. The UI function doesn't seem to let me save the whole table. Is there any other way around this? Thank you

[Agnius]

Why are you using some UI application to run a SQL script? You can use Execute SQL statement to do that. This will return the result into a data table variable for you.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.