Best way to Optimize get data from Excel files

[minhvuong93]

Dear all - I am new to PowerBi.

Each month I have a heavy files of Sales report stored under Excel files , consisting of multiple regions per month . Approx. 70MB per files per region.

I used Get dat from Folder in powerbi query and loaded them , however each time it takes about 15->30 min per month.

Each month I have a new folder like that and to append them together would be such a pain.

So I decided to store them separately as table, because each time I append New Data file, they reloaded the old sources as well (yes I tried to uncheck do not refresh = but not working - it still refreshes).

**So with each month under a different table in powerbi, what is the best way to summarize them (for example, Im just looking at Sum of Total Volume for each OutletCode per month. )

1st I create a new table called ASO All (basically total unique outlet IDs) : Distinct( Union (Distnct (outlet code June), distinct (outlet code _July) .etc.)

And then create the relationships from each Monthly sales table to this new table

Then basically dragged the volume from each month to the table.

I know this is not the most efficient way if the number of table are to increase....Any help would be much appreciated .

Pic 1: 

[v-yuezhe-msft]

@minhvuong93,

I would recommend you append all data into a single table in Power BI Desktop Query Editor. Then for each monthly  table(201806,201807,201808,etc.), you can disable "Enable load" option for them in Query Editor, this way, these tables will not be loaded into data view of Power BI Desktop. 


Also you are able to get Sum of Total Volume for each OutletCode per month in the appended table.

Regards,
Lydia

[minhvuong93]

@v-yuezhe-msft 

I tried append data but when PBI create the append table , it reloaded all the excel files again when I click apply query changes.

I disabled already the 3 modeling table in the query editors. How come it still need to reloaded from the excel files again 

And this took forever...

[v-yuezhe-msft]

@minhvuong93,

It will cost time when the append table contains large amount of data and columns. Please disable the highlighted options in your PBIX file and check if you can apply change.



Regards,
Lydia