Ideas Power Query and/or Python?

[mickeystanford_alumni]

Hi, I have an excel file with around 30 queries all referenced to different excel files. So 30 excel files, and a consolidated file where one of the sheets of each of those files goes here. Then, on my consolidated file I create a report with different figures and tables referencing each of the sheets. Let's say a way to create a consolidated report out of different excel files.

The problem I am facing is to refresh the data it is actually going quite slow, takes like 10-15min to refresh. Some of the sheets are pretty quick, others take so much time like my task manager goes to 4,000MB on excel taking 80% of memory.

Is there any way to improve the performance of the file?

I was thinking to create a python script to bring all those sheets into a consolidated sheet, and then querying just one sheet. I guess it will be faster, but I will have to change my name references...

Just if anyone has an idea on how to improve the performance without having to use pythong and then power query, that'd be very appreciated.

Thanks

 

[ExcelToDAX]

Where are these files located? SharePoint or a LAN or somewhere else?

 

[mickeystanford_alumni]

Where are these files located? SharePoint or a LAN or somewhere else

dropbox

 

[mickeystanford_alumni]

dropbox

also something I realised is when I go to the query on the right-hand side of Queries & Connection it says:
LOAD STATUS
Data loaded to worksheet
DATA SOURCES
Loading...

So seems there is a background thing running on the back, that's why my file has such a low performance?

 

[JEC]

It also depends on how you build your queries. Maybe you can improve by just changing your query steps to a more efficient solution

 

[mickeystanford_alumni]

It also depends on how you build your queries. Maybe you can improve by just changing your query steps to a more efficient solution

how could I do then? sending you the advanced editor, maybe you could help?

 

[JEC]

Unfortunately I do not have time currently. Search for best practices. For example, start with deleting all columns which you don’t need as first step, in evey query. So that your next steps have less to process. Also, take a look al table.buffer and list.buffer. Sometimes they can improve queries a lot by storing data in memory.

 

[mickeystanford_alumni]

Unfortunately I do not have time currently. Search for best practices. For example, start with deleting all columns which you don’t need as first step, in evey query. So that your next steps have less to process. Also, take a look al table.buffer and list.buffer. Sometimes they can improve queries a lot by storing data in memory

thanks for the response

any other with time to help?

 

[mickeystanford_alumni]

??