cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TheRealBrenton
Level: Powered On

Is there a better way to compare two tables (to replace MS Excel VLOOKUPS all day)?

Hello,

I am working on a project to automate some audits. I’m new to PowerApps, Flow, and Common Data Service so I’m not sure this idea would even work but figured I would throw it out there and see if any of you could point me in the right direction.

 

Vendor File

Each Friday the vendor emails a .xlsx file containing the benefit deduction amounts that should be deducted from each associate’s paycheck.

EmployeeNo

Paycode

Amount

Date

A123

QRS

21.2

6/7/2019

B123

TUV

100

6/7/2019

C123

LMN

12

6/7/2019

D123

LMN

12

6/7/2019

 

Internal File

The file is loaded into our system and a report (again .xlsx format) is generated prior to checks being cut.

EmployeeNo

Paycode

Amount

Date

A123

QRS

21.2

6/7/2019

B123

TUV

100

6/7/2019

C123

LMN

12

6/7/2019

E123

AAA

5.1

6/7/2019

F123

LMN

1000

6/7/2019

 

I need to compare these two files to make sure each row matches. In the example above, I would need to know that Employee E123 was loaded into the internal system but has the wrong amount (currently 5.1, should be 12). I would also need to know that Employee F123 is in the internal system with an amount of 1000 – but was not included in the vendor file.

 

Here is my rough plan – Please let me know if this would work…..or if you have a better idea/way to get this done.

  • Have all of the files emailed to a shared email box
  • Use Flow to identify these emails based on subject line. The Flow would then copy the attached .xlsx file to a Document Library on a SharePoint site.
  • Set up Flow triggered by a new file being added to SharePoint to load the file into a CDS entity
  • Figure out how to Join (or Compare) the Vendor Entity to the Internal Entity. These records would be added to a Errors Entity
  • Use Flow to email out a .xlsx file with the errors that need to be reviewed.
  • Use the results from these audits (or entity comparisons) to update a PowerBI dashboard so leadership can see the audits are being completed.

I had also considered making this into PowerApp but am a bit unclear on how many Plan 2 licensees we would need to purchase to use that solution.

Any ideas or feedback would be greatly appreciated!

1 REPLY 1

Re: Is there a better way to compare two tables (to replace MS Excel VLOOKUPS all day)?

Hi @TheRealBrenton,

 

First of all, interesting scenario Smiley Happy

 

I would simplify the first two steps by creating a Microsoft Team. If you do that you will get an e-mailaddress per channel and the attachment will be uploaded automatically to a document library folder called e-mail messages in the connected SharePoint site of the Microsoft Team.

 

Hope this helps a bit.

>