Best approach to dealing with aged data for Sharepoint Data files

[Ry009]

Hello

My current method of data connection is automatic exports from relevant systems into a SharePoint folder. The flow I use with Power Automate grabs the files from an inbox and overwrites the previous day's file with the same filename.

Eventually, I would like to show historical data (change by month). What is the best approach to this from a data naming and query perspective? This may be more of a power automate question, not a Power BI question, but thought I would pose the question to a beginner like myself that had progressed to something similar. 

[lbendlin · Accepted solution]

My recommendation would be to make the snapshot date part of the file name. That way you are not dependent on the created date or last modified date for the file (which may be misleading).  Then connect to the sharepoint folder from Power BI and ingest/combine all the files.  Use the date part as the unique file identifier in your Power BI data so you can then easily compare snapshots between dates.

[v-jingzhan-msft · Accepted solution]

Hi @Ry009 

@lbendlin has provided a nice practice. You can just follow that. As a supplement, here are some links you may find helpful:

Flow to automatically append date to filename in sharepoint whenever someone creates a new file or m...

Building a Flow to get an excel attachment from an email, upload to One Drive Folder and time stramp...

Power Query SharePoint folder connector - Power Query | Microsoft Learn

Best Regards,
Jing

[lbendlin · Accepted solution]

My recommendation would be to make the snapshot date part of the file name. That way you are not dependent on the created date or last modified date for the file (which may be misleading).  Then connect to the sharepoint folder from Power BI and ingest/combine all the files.  Use the date part as the unique file identifier in your Power BI data so you can then easily compare snapshots between dates.