Stored data deleted every hour

[Sgb1201]

Hey, 

I have an interesting problem. I'm doing some data visualization, where I'm using data from our database. However, everytime PowerBI updates the data, it only shows the data from the last hour. I.e. if the time is 10.10, the data will only date back to 09.10, which is a problem as I want the visualization to show the data on a day-to-day basis. 
The data is available if I manually select the date in the database, but everytime PowerBI "calls" the database, it only shows a "snapshot" of the last hour and deletes the preexisting data. 

 What would be the easiest way of dealing with this issue? The simplest solution would probably be to simply add the new data to the existing data with every update (and then make it refresh/delete everything in the evening -- around 22.00(?)) and then repeat the next day. Is this possible in PowerBI? Or is there another solution I'm missing? 

Apologies for any confusion and thanks in advance :-). 

Best regards, 

Sebastian 

[v-mengzhu-msft]

Hi @Sgb1201 ,

Do you mean that every time you refresh, the data changes to new data, but you still want to keep the original data?

If so, there is no direct way to stop this process, because for powe bi, your refresh is equivalent to re-importing the data, so data changes are inevitable.

If you stick with this idea, all you can do is copy the query and re-import it into a new, it seems to be more cumbersome.

Best regards,

Community Support Team Selina zhu

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

[Sgb1201]

Hi @v-mengzhu-msft, 

Thanks for the reply! 

Yes, exactly --Thats too bad. Would an easier solution perhaps be to make powerbi export the data into a local folder or sharepoint etc., and then get the data from there instead? 

[v-mengzhu-msft · Accepted solution]

Hi @Sgb1201 ,

Yes, you can do that. But be careful not to make changes to the values in the exported file, otherwise once you have used refresh, power bi desktop will update it again to the latest data. I would recommend that you have two copies of the file, one for the original data and one for the modifications, so that you can make sure that the data is kept up to date and can also be compared to the original data.

Best regards,

Community Support Team Selina zhu

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

[PVO3]

Hi Sgb1201,

Bet solution is switching to a Slowly Changing Dimension Type 2.

I don't think you can easily achieve this in Power BI. This should rather be applied in something like Azure.

Unfortunatly I don't think there will be an easy solution for this. 

[Sgb1201]

**bleep**. Oh well. I will look into it -- I'm not hearing you say that it's impossible! 

Thanks for the reply.