Forum Discussion
Creating Historical tables in Desktop
How would I go about creating a historical table in Power BI Desktop, that archives an entire table of day old data after automatic daily row entries are made from a live connection to a SQL Database. I understand I could write a query to create a history table within SQL, but due to permissions and convinience, this is not the best way for me.
-Thank you
2 Replies
Essentially I "want to collect your regularly loaded data without overwriting it and create a load log that writes the load activites with a timestamp into a new table'
There's no concept for this yet - but a feature request that you should support please: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7288623-incremental-data-loads
But there are workarounds: http://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or-power-bi/
If your data is more than 150k rows (the current limit of the R-export), you can create another workaround by compressing your data to JSON and create different table that holds the JSON-strings (who mustn't be longer than 1023 characters) and re-combine them (I've another blogpost about this technique outstanding, so if you need it, please let me know & I might push it a bit :-))
You can also export unlimited rows by using DAX-studio, but that would mean very manual export and is not so good for incremental stuff but better for one-time-tasks.
