Power Query Help

[YagamiDN]

Could you please help me? I am in a dilemma and can't resolve it. I have the following situation in Power BI: I have a Power BI report collecting data from a network folder, and every day the folder receives a new file.

What I want is for Power BI to identify the new file but keep the date from the previous file. The idea is to have a filter within the dashboard that shows which date you want to see the file version.

Example:

If a file is added today, in the column named "Creation Date," it will be 07/25/2023, and it will never change again.

Tomorrow, when I add another file to the column, only the new file's date will be shown as 07/26/2023, and it will never change again.

Basically, I believe that if I could have some function or method for Power Query to collect the metadata "creation date" of the files in the folder, it should work, but I am not managing to do so.

[edhans]

You would need to connect to the folder using the folder connector. Filter the file listing to keep the 2nd newest file. You can do this by:

Getting the latest file date, then filtering to include the files before that, then filter again to keep only the latest.

Then you will be down to one file, yesterday's. Now expand that file using teh File Combine option (the double arrows) and get the file date from inside the file.

[YagamiDN]

I do understand your idea, but it does not seem to work consistently.

Just to clarify what I needed is that within the query he made on the files, he adds a column, and in that column, it should automatically add the file creation date by extracting this information from the Excel metadata, considering that each file will have different dates.

This way, I can later create a filter to use in Power BI.

Please understand that a different file enters the folder every day, and it may have both duplicate and new data. Let me give you an example:

Yesterday the file had the following information:

Name: Renata / Status: In progress

Today

Name: Renata / Status: Completed

What I need is a way to keep both sets of information and have a filter that allows a person to see the status as it was on the previous day.

[edhans]

Is the previous day's file kept, or is it overwritten?

If kept, I am not sure I understand the problem as both files are there with the data you need.

If it is overwritten, you cannot do that. Power BI refreshes operate on the principle of wipe it all out and refresh with the latest data. You need a data warehouse to keep snapshots of data. Power BI doesn't do that. You could set up a DW in Fabric now that would have an automation to store snapshots of files on a daily basis.