Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
minhvuong93
Helper II
Helper II

Best way to Optimize get data from Excel files

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: 

 

 

3 REPLIES 3
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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. 
1.PNG

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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...

Capture.PNG

@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.

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors