Forum Discussion
Creating historical data with Pro subscription
Hi all,
I'd like to create trend analysis for a table that we pull the data from the source that is currently not going to a SQL server / database. The data doesn't include any timestamps, it's simply a list of controls and their attributes change over time (i.e. some may get removed or other's columns will be changing). We cannot implement a SQL server or a data warehouse/lake at the moment due to organisational constraints, but we can do it via sharepoint.
At the moment we have a table of controls that has 1300 rows. From my intuition the best way would be to connect the source to Sharepoint via an API and add a timestamp at the time of refresh (we will do it once a month). That way we can compare each month's number of controls. However, I'd like to know if there are any better options.
Thanks!
Hi Anonymous
- Power BI only reads data from the source at refresh time and does not store historical data. Historical data must exist elsewhere.
- If records are stored in a SharePoint list, it may be possible to access the historical versions of each item. https://www.youtube.com/watch?v=5wx79Z2Xr5Y
- Alternatively, if data revisions are stored separately, they can be saved in a SharePoint folder. Use the SharePoint folder connector to retrieve the files.
- Another option is to use Power Automate to run a query against the dataset a few minutes after the scheduled datetime refresh of the Power BI dataset. Save the query results to a SharePoint folder with filenames containing the query run timestamp.
The easiest way to do this, imo, is to query your source in Power Automate using whatever connector is applicable (Excel, Power BI, whatever the source is), then push that to a file (assuming you can't do a database). If you use the "create file" action with SharePoint, you can push whatever you query directly to a JSON file without any finangling. Then you can use the SharePoint folder connector to pull all of the file snapshots.
It's not super pretty or best practice, but if you're working outside of a database it's not that difficult to set up. I did a video on it for SharePoint sources here, the technique would be really similar for other sources: https://youtu.be/RBx-HbVpWTQ
2 Replies
Hi Anonymous
- Power BI only reads data from the source at refresh time and does not store historical data. Historical data must exist elsewhere.
- If records are stored in a SharePoint list, it may be possible to access the historical versions of each item. https://www.youtube.com/watch?v=5wx79Z2Xr5Y
- Alternatively, if data revisions are stored separately, they can be saved in a SharePoint folder. Use the SharePoint folder connector to retrieve the files.
- Another option is to use Power Automate to run a query against the dataset a few minutes after the scheduled datetime refresh of the Power BI dataset. Save the query results to a SharePoint folder with filenames containing the query run timestamp.
The easiest way to do this, imo, is to query your source in Power Automate using whatever connector is applicable (Excel, Power BI, whatever the source is), then push that to a file (assuming you can't do a database). If you use the "create file" action with SharePoint, you can push whatever you query directly to a JSON file without any finangling. Then you can use the SharePoint folder connector to pull all of the file snapshots.
It's not super pretty or best practice, but if you're working outside of a database it's not that difficult to set up. I did a video on it for SharePoint sources here, the technique would be really similar for other sources: https://youtu.be/RBx-HbVpWTQ