Append Table from SQL Query

[laurenmicrosoft]

I want to append rows to an existing table instead of overwriting it. The existing table is from Excel and I have already imported it to Power BI. Now I want to add new rows every month using a query to a SQL database. The tricky part is the SQL query is cumulative so refreshing the query will erase the previous month’s snapshot. I want a snapshot for each month collected in the same table.

The only workaround I have found is to run the query in SSMS, copy and paste the results to the bottom of the existing Excel table, and then refresh the Excel connection in Power BI. While this works, I would like a more automated process to refresh my Power BI dashboard.

[v-caliao-msft · Accepted solution]

@laurenmicrosoft,

When refresh data, Excel data source will reload to Power BI data model, so last time appended data are lost. In your scenario, you need to keep history data in Power BI data model which is not supported currently. You need to append the lastest data to history table and then load this history table to Power BI as you said.

Thank you for your understanding.

Regards,

Charlie Liao