Creating csv from the difference of two csv files

[Basso_324]

Hi all,

I'm currently trying to create a flow where the input is a CSV file that over each month accumulates more and more data. The flow is to take the difference between the most latest version of the data and subtract it from the previous run's data.

For example, a csv is submitted containing data up to the 15th of June and then the following day a new file is submitted containing all the same data AND the new data for the 16th of June. I just want the difference between the files to be created in a new separate csv. Each new day adds an unknown amount of new rows and the flow may not necessarily be triggered daily. These files can reach up to and exceed 100,000 rows of data by the end of the month so I would imagine doing a comparison row by row would take exponentially longer as the file grows over the month. At the start of the new month, the data starts fresh and is entirely different to the previous month's, so it would be a waste of time comparing completely different sets of data.

I've had a thought of having a simple text file or something that Power Automate reads and updates storing only the last row number of a non-empty row as a variable to use in subsequent runs so that it acts as a "bookmark" for where to start processing in the following run the next time the flow is triggered.

But then how would I get the flow to ignore this number if the next month starts?

I am currently thinking something like (IF Lastrunlastrow - newrunlastrow >= 50000 THEN process data as new month) since the previous months data is irrelevant for the current new month's data.

But this means if the user forgets, is ill or goes on leave long enough it might be possible to trigger this condition accidentally which would result in an undesired outcome. Not to mention that it might be possible for the last row to never reach 50,000 (in this example) in the previous month

My current thoughts are:

• It's not really possible to do such file manipulation in Power Automate from my research
• I'd probably be better off creating 3 seperate lists (One for new input, one for previous input and one for result) to achieve the result but not sure if it can be compiled into a csv.
  • Not to mention creating a sharepoint list from csv isn't possible from what I've read
  • May have to ask the user if it's possible to generate the files as an xlsx file instead
• Not sure on the best way to have the flow work on the change of the month (i.e 30th june --> 1st July) as the data in the following month would have none of the previous month's data and be starting fresh back at row 2 (row 1 being the headers)
• Flow doesn't necessarily always run daily so it needs to be smart enough to recognise the jump in dates (e.g 15th of june --> 20th of june)
• Not sure if simply "storing" and updating the row number for the flow to read in for processing would speed up the process especially as the files get larger as the month progresses
• The data itself isn't really unique, it does contain a column for the date and car registrations in a 1 to 1 relationship (i.e for each date, a car's number plate appears only once)
  
  • E.g 100 rows of 3rd of March each with 100 unique car licence plates.
  • Then 95 rows of 4th of March with 95 car licence plates, most of which already appeared for the 3rd

Would greatly appreciate any insights or suggestions for this.

Worst case scenario, I can tell the user such a request isn't possible to fully automate but perhaps some of it can be.