Forum Discussion
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
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.
