Best practice advice please

[jimrward]

I have a large dataset which up until 1st April is static with historical data (BaseData)
periodically I want to append data to this table from a folder say once a month, so i will create incremental files and load from folder into a separate table and then run an append query to add it to the BaseData
I will then run all of my queries I have on the (BaseDataPlusIncremental) table

question, is it better to produce an intermediate table called append which is a simple Table.Combine(BaseData,Incremental) and refer to this in my processing queries that follow
OR
make the the source for my processing query a Table.Combine, this will save bloating my workbook with 150k lines of data

if I load the base into a table and delete the query so i am effectively making it static will this improve efficiency of processing

Lastly do I make any of these queries connection only or load to table, will this impact the speed/optimisation

 

[Matt Allington]

You don’t mention if you’re using PBI or Power Pivot. PBI has additional options to turn off refresh, but they won’t help you here.

In my experience:
I create a history file whenever the file combine is unbearably slow. I normally do a full refresh and then export from the data model using DAX studio to a CSV file. I load this CSV file as the history file and do a file combine on the new data, appending as you suggest. It makes no performance difference where you do the join. I normally create 2 queries, one to load the history, one for file combine, then append them.
If you delete the query, the table will be deleted, too, so don’t do that. There is no way to load a static table and have it not refresh if you then proceed to append it to another table. This is a flaw IMO
Setting to connection only or load to table makes no difference.

 

[jimrward]

@Matt Allington using Power query in excel with no pivot tables
I removed the intermediate combine step which removed the double bloat of my excel workbook, and made the source of my first query a Table.Combine(base,extra) however it still insists on pulling in the base file from its original csv, shame you cant mark tables as static, so its processing engine knows to just take the contents as is.
Surprised that PQ takes a long time to process the get from folder for 3 small csv files with an accumalative of 400 lines, strange thing is if i step into the query it is quite quick when run as a refresh takes a long time, i have turned off background stuff and i gain a bit more processing speed
The first query splits out the data into sub tables for want of a better phrase I then run further sub queries on each subset I have around 50 sub queries, so i have put together a vba script to process them sequentially in the order that i want them to run to produce the mini tables I want that I can summarise by copying the contents to an A3 sized summary