Performance of Excel vs Database Data Source

[Anonymous]

I'm currently having issues with my PBIX performance (It always crashes). In my current setup, I'm using 4 Excel files with multiple sheets as data sources. But I didn't experience these crashes before when I'm connecting to around 3 tables from a Database (SQL). And the number of rows is much larger in the database compared to what I have in Excel. 

Is there a whitepaper or reference relating to Power BI performance when considering what data source to use (particularly Excel and database)? Or do you have any idea if a type of data source can be a factor to performance?

FYR, here's my system setup:

16GB Ram

64 bit 

[v-shex-msft · Accepted solution]

HI @Anonymous,

AFAIK, If you only get data from excel and merge them, it should works well without any performance issues.

Have you do any advanced operation on these query?(e.g. invoke custom function, refer any query table in calculate, refer previous query steps)

These operations may cause the memory issue.(power query will spend memory resource multiple times to handle each row calculation)

If that is a case you can consider to use list.buffer or table.buffer to cache reference source to memory to reduce cost of resource.

How and When to use List & Table Buffer?

Regards,

Xiaoxin Sheng

[Anonymous]

Right, I have operations which refers to previous query steps. 

Thanks @v-shex-msft! I'll check out table.buffer.