Identify new rows on the table and create a collumn with their load dates

[NGI]

Hello Guys. I really need your help on this. Maybe it's easy, but i am not getting it.

I have some txt files that I take from an open base in a website. So I keep thos files in the computer as my database. I use a scrip in Python to download everyday the new file, and it overwrites the old file.

So, I really need to know which ones are the new rows of the day and, for my purposes, I need to write the date that this new file was downloaded.

I am thinking in something like copying the table and compare, but I am not sure how to create this date. I've tried to use data.local.now and datelocalfixed, but they updated the value after a refresh, they did not keep.

Can you guys give me a hand? It would very aprecciated.

If I was not clear, please feel free to say it. 

Thanks in advance.

[Nolock]

Hi @NGI,

Power BI doesn't keep any historical changes. Every refresh replaces the whole internal database. It means you have to store all versions on your disk.

Can your Python script do that for you? Can the script create a new file every day instead of replacing it? Can a new file contain only data from the last day? Do your records have a timestamp or an ID?

If the file name contains a timestamp, you can parse a date from the file name. Or, but not recommended, you can also use file properties like date created or date modified when using the Folder data connector.

Many questions, I know, but it helps us to find a solution much faster.

[NGI]

Hey @Nolock , Thanks for your reply. Here are the answers:

Can your Python script do that for you?

I think we can try something like this, but we were trying some solution in pwoer query.

Can the script create a new file every day instead of replacing it?

Yes, it can, but they are big files...

Can a new file contain only data from the last day?

Yes it can, but it would be too many files to keep...

Do your records have a timestamp or an ID?

No. The key is a three collumns combination.

Any other ideas using power query? Thanks again!

[Nolock]

Hi @NGI,

thanks for your answers.

I'd like to suggest 2 options:

1. Your Python script creates a new file every day. This file has a proper name containing a date. You will have many files, but it isn't a problem to merge them all in PowerQuery. Use Folder Connector and merge all files together.
2. You modify your Python script which adds newly a timestamp to every row when created. And you append new rows to an existing file. You have only one file and you know when a row was appended.

Which one do you prefer?