Forum Discussion
Save old and append updated data into an additional table
Hi,
I am using Power BI Desktop connected to a table in a database whose data get updated daily. The old data is overwritten with the new. I would like to store the old data so that I can use it in Power BI Desktop. I was thinking to extract data from my database table, load it and save it into another table in Power BI Desktop so that when I extract the data tomorrow I can just load it and append it to this extra table in Power BI Desktop.
What I need is some kind of "static" table that does not get refreshed and I can populate with new data by appending.
Is there a way to do something like this in Power BI?
Many thanks in advance!
THERE ARE TWO STEPS TO ACHIEVE THIS.
STEP1:
CONNECT YOUR POWERBI MODEL TO DAX STUDIO(YOU NEED TO DOWNLOAD SEPARATELY)
EXTRACT THE TABLE DATA INTO .TXT FILE USING BELOW SYNTAX
EVALUATE
TABLE NAME
PUT YOUR EXTRACTED DATA INTO ONE FOLDER.
CREATE A CONNECTION IN POWERBU DESKTOP "FROM FOLDER" OPTION.
EVERYDAY REPEAT THE FIRST PROCESS WITH DAX STUDIO AND PUT YOUR FILE IN FOLDER. REFRESHING THE POWERBI DESKTOP FILE WILL PICK THE NEW ROWS( APPEND CAN ALSO BE DONE)
9 Replies
You can import first day’s data into one excel sheet from database. When in second day, you can use the existing connection to load the new data in another new sheet in the same excel file. When you want to combine these data into one table, you can get data from excel file with Power BI Desktop by selecting all sheets exist in this excel file. Then in Query Editor, just combine the imported sheets use the “Append Queries as New” function. In third day, you only need to import the new sheet into Power BI Desktop, and combine this new table to the existing Merged table.
BTW, you can vote this idea to push it supported in PBI Desktop in the future.
Best Regards,
Herbert
v-haibl-msftis there a way to have every refresh automatically load to a new sheet? So that if I do 7 refreshes, 7 new Sheets will appear with the queries content at each refresh? Thank you.
Hi
You could try loading a copy of your data and setting it not to load then appending it to your new data on refresh? i believe if the data is set not to load it will not refresh.
Mike
THERE ARE TWO STEPS TO ACHIEVE THIS.
STEP1:
CONNECT YOUR POWERBI MODEL TO DAX STUDIO(YOU NEED TO DOWNLOAD SEPARATELY)
EXTRACT THE TABLE DATA INTO .TXT FILE USING BELOW SYNTAX
EVALUATE
TABLE NAME
PUT YOUR EXTRACTED DATA INTO ONE FOLDER.
CREATE A CONNECTION IN POWERBU DESKTOP "FROM FOLDER" OPTION.
EVERYDAY REPEAT THE FIRST PROCESS WITH DAX STUDIO AND PUT YOUR FILE IN FOLDER. REFRESHING THE POWERBI DESKTOP FILE WILL PICK THE NEW ROWS( APPEND CAN ALSO BE DONE)
Thank you for the detailed explanation!
I followed the steps you describe and it works!
BhaveshPatel : I tried your solution and it works! Thank you!
Anonymous : I have also checked Imke Feldmann's post before writing in the forum. It seems like the right solution to this problem but I was wondering if there are also other workarounds. Thank you!v-haibl-msft : Nice suggestion! I voted the idea to improve Power BI. Thanks!
Imke Feldmann has a pattern for this using R: http://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or-power-bi/
You can now call R from Power Query (without needing the plot in a R visual). I'm not sure what performance or relaibility of this technique is but, if it si sustainable, it lets you maintain your historical data autmatically as part of your data refresh cycle.
