Update history data

[Syndicate_Admin]

Good morning everyone!!

See if it's possible to solve my problem. Turns out I have an Excel saved on OneDrive, which is a data history from the beginning until March of this year. In this same cloud another Excel will be saved every month (it will be crushed every month) so that it is programmed in Power Bi that that data from the last month will be cleaned up and joined to those of the history. My problem comes as soon as the next month arrives, as this data has not been saved in history, and instead of having it from the beginning to the month of April of this year for example, it would have from the beginning until March and now May (it would skip April because it was not stored as such in the historical). After several tests, I understand that it would work if physically that historical Ondrive Excel had updated the data for that last April that was appended to PowerBi. My question is:

- Could you have to have the result of that union of the last month with the historical one automatically saved in the cloud from powerBi, without having to do it by hand?

- Otherwise, is it really possible to do the automation I intend?

I hope you've been able to explain myself correctly.

A greeting and thank you in advance for your help.

[amitchandak · Accepted solution]

@Syndicate_Admin , there is a blog on Dax append method. Based on what I got I think this can help

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

[BA_Pete · Accepted solution]

Hi @andynedine ,

It sounds like you need to use incremental refresh. You can read the details here.

In summary, incremental refresh will essentially add your monthly data update to any data you already hold in the dataset, providing you give it the correct paramaters/criteria to evaluate new/old data against.

There's some limitations and caveats that the linked article will gve you details on, but probably the most important is that, once you have assigned a report for incremental refresh, you can no longer update it in Power BI Desktop. Therefore, before you go ahead, you must be absolutely sure that your report is in its final state and will stand the test of time.

Pete

[amitchandak · Accepted solution]

@Syndicate_Admin , there is a blog on Dax append method. Based on what I got I think this can help

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/