Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

undefined

Good!

I've gotten stuck with an idea of implementing a data flow in a Power BI report, to see if you could help me, please.

The problem is as follows:

I have a query against SQL Server that turns me into a table. This table is updated daily. I need a daily copy of the table with the changes I have undergone to create a history of states through which the data passes. The goal is to update the table query daily and have that data appended to the historical table.

I've tried incremental updates but they overwrite the query. I have also tried the option not to update the tables that I want to keep as historical, but then they are not appended.

I am sure that something I am not doing well, so I ask for your help.

Thank you very much in advance!

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Syndicate_Admin ,

     

    Actually to my knowledge, Power BI is a "read only" system so it doesn't have the ability to store data beyond a refresh. If possible, you should stage a copy of each day’s data in a relational database (ie build that data warehouse) or even as text files in a folder (Power Automate may be useful to do this)

     

    For more information, you could refer to this blog——Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using Incremental Refresh

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Syndicate_Admin ,

     

    Could you tell me if my reply posted before helps you? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it. 

     

    Best Regards,
    Eyelyn Qin

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Good morning!

    I really appreciate all the help provided, however, I have not managed to mount the process through Incremental Updates. I think something is wrong with me in creating the process that way, but I don't know what it can be.

    Finally, I had to set up the process in parts, which I tell you in case they were helpful on occasion or if you see that there is a better way. The process has been concluded as follows:

    1. Data flow that updates the table obtained via OData connection.

    2. Connecting Excel to the previous Flow to update that day's data.

    3. Power Automate scheduled flow to convert Excel to HTML as a daily file to generate a repository.

    4. Connection of Excel to folder of HTML files to have the accumulated historical.

    5. Connect Power BI to Excel_Histórico to get all the information.

    I know that it is an inefficient process, which also requires that the connection to the Gateway be active (computer on), but it is the only thing I have been able to mount with the available means.

    I wish I could improve it, but the reality is that I don't quite know how to do it, so any help will be welcome.

    Many thanks to all for your interest and help!!