compare two spreadsheets and update data on master spreadsheet

[awp426]

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  

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

[Anonymous]

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]

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]

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.

[awp426]

Thanks for your help, will have a try : (

[awp426]

Hi Michael 
thanks for your suggestion : )