Keeping data ‘fresh’
I’ve been thinking about what makes a good Power BI report by trying to understand how best to present the information to the audience. When I’m looking at a dashboard that shows the latest pupil data, or attendance data, or assessment data, one key piece of information that I need is an indication of the freshness of the data – in other words I want to know exactly when this data was last exported from the pupil database.
The secret is in the csv file metadata…
Working mostly with the SIMS pupil database, I usually export the data to a csv file located on OneDrive. The csv file metadata holds the ‘modified’ date, which is usually the exact time and date when the data was last extracted from SIMS. This is therefore the ideal date to use when indicating freshness to the user. But how to get access to this date?
Importing file and folder data into a the data model
The process is surprisingly easy – we can use ‘Get Data’ in Power BI to import the file metadata into a table. A table or card visualisation is ideal to display the data.
- In Power BI use ‘Get Data’ and select the ‘folder’ option.
- Browse to the folder that holds your csv files
- This will create a new table in your data model, named after the original folder, which can be confusing so…
- Rename the table to something more appropriate (eg ‘csv file import dates’)
- If you wish you can hide this new table from the report – most users don’t need to know it exists.
- Use a table visualisation to give a list of all the files in the directory
- Or use a card visualisation to pick out the date from just one file (you can use a visual level filter to pick just one file date for the card