cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
astarner Frequent Visitor
Frequent Visitor

Append new data to a history table without overwriting

I have created a history table with 2 years of data (copy and paste to create the table).  I have then created another query that will pull the auto created datasource containing new information to be formatted and what remains is only what i need.  I need to append one to the other on a weekly basis AND THEN STORE THIS INFO, without overwriting.  I need to build new history to continue to add to the history table.  In power query editor, no matter which way i slice this i cannot get it to SAVE the most recent data without overwriting it.  In my simple mind, i need to copy the new results and paste to the history table.  Note that this is a HANDS OFF query, i need it all to be automated.  Tons of posts everywhere about this, however i have yet to find a logical solution.  Any ideas?  Willing to attempt anything at this point!!! PLEASE!!!!!!

6 REPLIES 6
Super User
Super User

Re: Append new data to a history table without overwriting

Hi @astarner,

without any example it was difficult to follow you, but I think you need the function Table.Combine, which appends tables one after each other.

If it doesn't solve your problem, please add an example what you actually have and what you expect. Thank you 🙂

Super User
Super User

Re: Append new data to a history table without overwriting

Sounds like you're looking for incremental load? This feature is available in Power BI Premium. If you don't have Premium, the workarounds depend on if you want to refresh it in the service or not. So do you want a solution that works in the service?

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
astarner Frequent Visitor
Frequent Visitor

Re: Append new data to a history table without overwriting

I have MANY files (70+) that dump out of our ERP system daily and stored in sharepoint (tsv files).  7 of these files are point in time files where they are only good for the minute that they were ran.  for example, 1 file is open PO's.  I have power BI pulling in this file and formatting it.  Because i do not have another way to store the history, i have another query that is just a table that i have typed in the history on a weekly basis.  The end result of the daily dump is a query with 2 columns (a date, and a number), the history file is exactly the same (a date and a number).  I need to append the newly formatted file (query) to the history query on a weekly basis and not keep overwriting itself.  End goal is 1 query with weekly updates.  Sounds simple - but i cannot wrap my head around this!  Thank you for your help.

Super User
Super User

Re: Append new data to a history table without overwriting

Safest thing is to export the consolidated data via R or Python to a file and append new data with every refresh. That would work in the service as well: https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or... 

 

Other worakaround that just work for Desktop are these: https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/ or this: https://www.thebiccountant.com/2016/11/15/incremental-load-powerbi/ 

 

Just be aware that they all have their drawbacks.

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




astarner Frequent Visitor
Frequent Visitor

Re: Append new data to a history table without overwriting

I  just made a go at the union - (joining the History and the New Data tables).  I see that it did combine them as expected - so i ran a new report out the the ERP system, and then did a refresh.  Almost as expected, it omitted what WAS in the new data query with todays data and THEN it joined this to the history file (so i am missing a week of data). I almost need to join them, create a new table with hard data and then have that cycle repeat weekly - however i do not believe there is a way to automate that..... is it?

Super User
Super User

Re: Append new data to a history table without overwriting

No, you have to do this all manually. These are all pretty fiddly workarounds unfortunately.

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)