How to build up fact table from scheduled snapshots

[PowerWhy]

Hello,

I have power bi linked to a table of customers. It 2 fields, the customer ID and the customer status (subscribed or unsubscribed). There are no datestamps in the table.

Is there a way I can get power bi (on autorefresh) to scan this table once a day and append all the rows to the fact table in power bi, and populate a field with the snapshot date stamp. So that it would be a stacked table with all the rows of the customer table replicated, with each replication having the snapshot date showing in a date field?

Many thanks for your help,

PW 

[v-nmadadi-msft]

Hi @PowerWhy ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the Community members for the issue worked. If our response addressed, please mark it as Accept as solution if you found it helpful.

Thanks ands regards

[v-nmadadi-msft]

Hi @PowerWhy ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-nmadadi-msft]

Hi @PowerWhy 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[rohit1991]

Power BI doesn't natively support appending daily snapshots with timestamps during auto-refresh. To achieve this, consider the following approach:

1. External Data Storage:

   • Set up an external database (e.g., SQL Server, Azure SQL) to store daily snapshots.

2. Automate Data Capture with Power Automate:

   • Create a flow in Power Automate to:
     • Extract daily data from your customer table.
     • Append this data to the external database, adding a current date timestamp.
   • This setup ensures each day's data is stored with a corresponding snapshot date.

3. Connect Power BI to the Historical Data:

   • In Power BI, connect to the external database to access the accumulated historical data for analysis and reporting.

This method allows you to maintain a historical record of daily snapshots, enabling trend analysis over time.

for further reference please visit --> https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
and
https://jd-bots.com/2020/10/24/insert-rows-in-the-azure-sql-db-table-using-power-automate-send-api-r...

[v-nmadadi-msft · Accepted solution]

Hi @PowerWhy  ,
Thanks for reaching out to the Microsoft fabric community forum.

I would recommend using dataflows.
After extracting data from your source
Use this command DateTime.LocalNow() in custom column, this will create a snapshot of data and time.

In dataflow we can configure destination, Use warehouse or lakehouse as the destination.
There you can configure the table to append

You can use pipeline to schedule refresh the dataflow

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to give "Kudos"

Thanks and Regards

[lbendlin]

Power BI has no memory* . You need to manage your snapshots in the upstream system. * Yes, there is the self referencing option but that has no safety net.

[rubayatyasmin]

Hi, @PowerWhy 

In PQ, open the Customer table, and add a custom column using DateTime.LocalNow(). This custom column will capture the current datetime during each refresh. 

Then append this table to your fact table. this should help achieve your result.