Updating Row based on another excel, add new rows if not present

[remabsh]

Hi,

I'm new to power-automate, so I wanted to ask if anyone knew how I could potentially execute the following or if it is even possible. [Sorry if this is too long/detailed]

Scenario: I want to create an inventory stock control of our supplies. Currently, the stock is dispensed/recorded in my company's secure web application, but the web application is not able to give any indication/alert that the stock needs to be re-ordered. 

Desired outcome: I want to create a report in Excel (that i will later put in Power BI) that will show stock on hand, re-order level and re-order quantity (based on last 3 month sales and annual sales history), and when the stock reaches the re-order level, a notification via MS Teams message is sent.

My thought process to execute this:

>> Create a master excel workbook in one-drive that contains the following columns

Supply code*, supply name, supply size, vendor name, current stock on hand, sales from last 3 months, sales from last year, re-order level, re-order quantity 

*The supply code will be unique to each product

>> An automatic excel report will be generated twice a day from the web application that contains the columns underlined above and will be placed in one-drive. Power automate will have to compare each supply code from the automated report, and based on "supply code", update the master excel workbook "stock on hand" column

• if the automated excel report has a new code that isn't in the master excel, it should create a new row and fill the information of the underlined columns above (which will be available in the automated excel report)

>> for the 3 month sales history and annual sales history, should I generate an automatic excel report from the web application, let power automate copy/paste the rows to a new master excel workbook, and add a formula that calculates the sum of each sale then create another flow to extract this information and put it in the original Master excel sheet? 

• once this part is figured out, I would need to put a formula in the original master excel sheet to dynamically calculate the re-order level/quantity based one historical sales

>> Finally, once the stock on hand = re-order level, a MS Teams message should be sent to the team to review

Your advice is appreciated!

[momlo]

Hi @remabsh 

After reading below, I would check i fi can connect Power BI to your corp app db directly and then build dashboards there. This way you do not need to automate UI and have reliable solution. You could use Power Automate to trigger the Teams/email alerts too when necessary.

Desired outcome: I want to create a report in Excel (that i will later put in Power BI) that will show stock on hand, re-order level and re-order quantity (based on last 3 month sales and annual sales history), and when the stock reaches the re-order level, a notification via MS Teams message is sent.

[remabsh]

Currently, that option is not available which is why I would need to pull automated reports from the database and use the information to build a dashboard.

[momlo]

Hi @remabsh 

I scanned your plan and it seems ok (i didn't focus on details thought).

So my advice is - just try it out and see were it works fine , where it is failing for some reasons, where you see problems and reach out if needed 🙂