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

Compare Daily csv files

Good Day, 

 

Looking for the best way to approach the following: 

1. We will have one master csv file with a few thousand rows. then we combine the first two columns to make a unqiue identifier for each row.  Example is   192.168.1.1-340101

2. Daily we will get a new csv file that we will have to combine the first two columns to make a unqiue identifier.  

3. We then compare the two files.  Identify anything that doesn't currently exist in the master csv.

4. We then update the master csv with the new findings

My question is can we do this with power bi so basically it is doing the combinding, updating and comparing for us and we are just placing the daily csv file in a folder ? 

1 REPLY 1
edhans Super Contributor
Super Contributor

Re: Compare Daily csv files

You can do this in Power Query.

  • Combining the first two colums to make a unique identifier is pretty simple. You can actually just select those columns, select teh Add Column ribbon, and "Merge Columns." You can then tweak how it looks in the formula bar if it doesn't look exactly like you want.
  • You'll do a "Right Anti Merge" to identify any records in the 2nd CSV file that don't exist in the Master CSV file.
  • You can then append those results to the master CSV query. You'd create a reference to the Master CSV query, then Append Queries to the merged query above.

 

The problem is if you do this with the Master CSV file and File A on Monday, if you do it with Master CSV file and File B on Tuesday, File A will no longer be in the mix. Power Query will NOT actually modify your source files. If you want it to be additive, you'd need to actually copy the results of the Right Anti-Merge and then Paste them to the actual CSV file using Notepad or similar text editor.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 6 members 2,749 guests
Please welcome our newest community members: