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.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Users Online
Currently online: 54 members 3,397 guests
Please welcome our newest community members: