cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
awp426
New Member

compare two spreadsheets and update data on master spreadsheet

Hello all

 

Happy new year and hope you had a great break

 

Could you please advise how to do the below tasks with Power Automate /desktop?  Have tried lots but no luck with it  22.jpg

 

We have 2 spreadsheets,  Spreadsheet-A and Spreadsheet-B, Power BI will connect and read data from spreadsheet-A,  every day, we export data from website and generate it as spreadsheet B (by power automate desktop), then we will compare data (by ID) and update spreadsheet-A with the below conditions

  • If the status is Closed on Spreadsheet -A, no update is required.  (e.g. ID 4 and 5 )
  • If the status is not Closed on Spreadsheet -A, and it is closed on Spreadsheet-B, we change status to closed copy date from Spreadsheet B to A (e.g. ID 2)
  • For new ID on Spreadsheet-B, copy the whole row to spreadsheet-A

Since it will be a daily task,  not sure how to do it with power automate desktop/power automate if it is possible to do it with Power BI, please let me know  thanks

5 REPLIES 5
Anonymous
Not applicable

Hi awp426,

 

After having a look into your requirement, from my point of view, it should be done by using PAD. So just give you bold advise below.

 

  • If the status is Closed on Spreadsheet -A, no update is required.  (e.g. ID 4 and 5 ) >> There is no actions to take as no update is required based on your point mentioned.
  • If the status is not Closed on Spreadsheet -A, and it is closed on Spreadsheet-B, we change status to closed copy date from Spreadsheet B to A (e.g. ID 2)>>>>Do a loop on the datatable of spreadsheet-A by using Read from excel worksheet, then set a condition that status is not equal to "closed". After that, it should work out the item you wanted. Based on this item, you have to do another loop on the datatable of spreadsheet-B by using Read from excel worksheet. And then, you also set a condition that ID of Item from Spreadsheet-A is equal to the ID from spreadsheet-B Item. After found it, you should get the date of it and then exit this loop.  At last, update the date value just now you got and change status value to "Close" from the first loop.
  • For new ID on Spreadsheet-B, copy the whole row to spreadsheet-A>>>>it can be set a part of above solution after doing 2 loops into spreadsheet-A and -B. To found the item ID of Spreadsheet-A if  duplicated with Spreadsheet-B, if true, follow the above solution just I mentioned continually. If false, update the datatable of spreadsheet-A for this item which should be fine.

Hope it helps.

Please mark it answered if you are satisfied.

 

Thanks.

MichaelAnnis
Super User
Super User

I apologize. I’m on my phone, so I may be shooting some from the hip on some syntax here.  I don’t know Power BI, but I’ll give you the Excel answer and the logic will be the same:

 
Attach to Excel SheetA
Get %FirstFreeRowA% %FirstFreeColumnA%
Read from Excel A1 to %FirstFreeColumnA - 1% %FirstFreeRowA - 1% to %ExcelDataA% (with a title row)
 
Attach to Excel SheetB
Get %FirstFreeRowB% %FirstFreeColumnB%
Read from Excel A1 to %FirstFreeColumnB - 1% %FirstFreeRowB - 1% to %ExcelDataB% (with a title row)
 
Loop 0 to %ExcelDataA.RowsCount - 1% increment of 1 As %LoopIndexA%
    If %ExcelDataA[LoopIndexA][‘Status’]=Closed
        Next
    Else
        Set variable %CurrentID% to %ExcelDataA[LoopIndexA][‘ID’]%
        Loop 0 to %ExcelDataB.RowsCount - 1% increment of 1 As %LoopIndexB%
            Attach to ExcelB
            If %CurrentID% = %ExcelDataB[LoopIndexB][‘ID’]
                If %ExcelDataB[LoopIndexB][‘Status’]=Closed
                    Set variable %CloseDateB% to %ExcelData[LoopIndexB][‘date’]
                    Attach to ExcelA
                    Write to Excel ‘Closed’ Row=%LoopIndexA + 1% Column=B
                    Write to Excel %CloseDateB% Row=%LoopIndexA + 1% Column=C
                END(IF)
            END(IF)
        END(LOOP)
    END(IF)
END(LOOP)
 
‘as of now, parts 1 and 2 are done, we just need to get new rows of B added to the bottom of A.
 
Set variable %LastRowA% to %FirstFreeRowA - 1%
Loop 0 to %ExcelDataB.RowsCount - 1% increment of 1 As %LoopIndexB%
    Set variable %CurrentID% to %ExcelDataB[LoopIndexB][‘ID’]%
    Loop 0 to %ExcelDataA.RowsCount - 1% increment of 1 As %LoopIndexA%
        Attach to ExcelA
        If %CurrentID% = %ExcelDataA[LoopIndexA][‘ID’]
            Go To Label ‘Match’
        END(IF)
    END(LOOP)
    Set variable %IDB% to %ExcelDataB[LoopIndexB][‘ID’]
    Set variable %StatusB% to %ExcelDataB[LoopIndexB][‘Status’]
    Set variable %DateB% to %ExcelDataB[LoopIndexB][‘date’]
    Write to ExcelA %IDB% to Row=%LastRowA + 1% Column=A
    Write to ExcelA %StatusB% to Row=%LastRowA + 1% Column=B
    Write to ExcelA %DateB% to Row=%LastRowA + 1% Column=C
    Set variable %LastRowA% to %LastRowA + 1%
    Label ‘Match’
End(Loop)
Benny_1857
Helper III
Helper III

I fill very strange if you use PBI, it is a very easy  outcome you can get from Power Query.
Loading A and B into PQ, Merage B with A (Full Outer) as B has more rows of data.
With 2 if then else you can get what you need. 
Then use UI flow to refresh this PQ model once you update A and B.

I load the file directly from excel file.

You could load them from 2 different folder. Folder A and B. Once updated, clear the file in A and B, then save the newest file into A for next time.

Below pics are the basic steps in Excel Power Query. I think the guy who loading data into PBI must understand it.

Benny_1857_1-1642308376072.png

 

Benny_1857_0-1642308339449.png

Benny_1857_2-1642308508905.png

Benny_1857_3-1642308560772.png

 

Thanks for your help, will have a try : (

Hi Michael 
thanks for your suggestion : )

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (2,071)