Forum Discussion
Helper IHow to automatically transfer data from a semantic model to a new table
Hi lafakios,
Power BI does not have any native option for taking snapshots of data at a given point of time. So you'll have to make use of some staging area or database where you can hold such data.
Generally, this is the capability of a data warehouse. For example, in our organization, we have a sophisticated data warehouse implementation which is being used for this purpose and a lot more.
Below are some of the solutions you can start thinking about for achieving your desired results -
- BEST: Implement an External Storage (like Azure Data Warehouse, or Azure Data Lake or Databricks) along with an ETL process (like using Azure Data Factory or python scripts) to snapshot and load data in the external storage
- Good: Power Automate - You can use Power Automate to query your data source or even your Power BI Dataset itself, take monthly snapshots, and store them in CSV or text files. You can then use this snapshot data in your report as required. The following video can be referred to for implementing this solution - https://www.youtube.com/watch?v=SOQeNnSwp60&ab_channel=NextGenBIGuru
- Not Recommended: Use Power Query and Incremental Refresh - Though the purpose of incremental refresh is to improve model refresh performance by archiving older data and refreshing a smaller range of data, it can be intelligently used as a workaround for achieving your requirements. I can explain it here, but it would be better if you could simply refer to Chris Webb's blog for this - https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
- Can refer to this as well - https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
So you now have a couple of ways to implement your desired solution and achieve your requirements. Implementing a Data Warehouse or proper external storage can have barriers, so I would recommend looking at the Power Automate solution more seriously.
Do let me know if you have any questions or need any help!
Solution SageHi lafakios,
Power BI does not have any native option for taking snapshots of data at a given point of time. So you'll have to make use of some staging area or database where you can hold such data.
Generally, this is the capability of a data warehouse. For example, in our organization, we have a sophisticated data warehouse implementation which is being used for this purpose and a lot more.
Below are some of the solutions you can start thinking about for achieving your desired results -
- BEST: Implement an External Storage (like Azure Data Warehouse, or Azure Data Lake or Databricks) along with an ETL process (like using Azure Data Factory or python scripts) to snapshot and load data in the external storage
- Good: Power Automate - You can use Power Automate to query your data source or even your Power BI Dataset itself, take monthly snapshots, and store them in CSV or text files. You can then use this snapshot data in your report as required. The following video can be referred to for implementing this solution - https://www.youtube.com/watch?v=SOQeNnSwp60&ab_channel=NextGenBIGuru
- Not Recommended: Use Power Query and Incremental Refresh - Though the purpose of incremental refresh is to improve model refresh performance by archiving older data and refreshing a smaller range of data, it can be intelligently used as a workaround for achieving your requirements. I can explain it here, but it would be better if you could simply refer to Chris Webb's blog for this - https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
- Can refer to this as well - https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
So you now have a couple of ways to implement your desired solution and achieve your requirements. Implementing a Data Warehouse or proper external storage can have barriers, so I would recommend looking at the Power Automate solution more seriously.
Do let me know if you have any questions or need any help!