Refreshing takes too long: SQL combined multi sheets in 1 sheet.

[nawaf]

Hello friends, hope allis well!



The below code uses aSQL code to combine multi sheets (workbooks) into one sheet. This is happening on PowerPivot.
I got the idea from https://www.contextures.com/PowerPivot-Identical-Excel-Files.html

Yes, there are manyrows (5 million) & 17 columns; and refreshing is taking a lotof time.
What can I do speed up the refreshing the data of the powerpivot?

btw my computer has good specs:

Windows 8.1 Pro

Installed memory (RAM)14GB

System Type: 64- bit OS,x64-based processor



Please help, and tell me howcan I speed up the refreshing process.



Thanks a lot in advance!

SELECT [HEAD$].*   FROM [HEAD$]

UNION ALL 

SELECT * FROM`F:\DATABASES\AvgClaimCo\17-18.xlsb`.[JAN-APR$]

UNION ALL 
SELECT * FROM`F:\DATABASES\AvgClaimCo\17-18.xlsb`.[MAY-AUG$]

UNION ALL 
SELECT * FROM`F:\DATABASES\AvgClaimCo\17-18.xlsb`.[SEP-DEC$]

UNION ALL 
SELECT * FROM`F:\DATABASES\AvgClaimCo\16.xlsb`.[JAN-MAR$]

UNION ALL 
SELECT * FROM`F:\DATABASES\AvgClaimCo\16.xlsb`.[APR-SEP$]

UNION ALL 
SELECT * FROM`F:\DATABASES\AvgClaimCo\16.xlsb`.[OCT-DEC$]

UNION ALL 
SELECT * FROM`F:\DATABASES\AvgClaimCo\15.xlsb`.[Jan-Mar$]

UNION ALL 
SELECT * FROM`F:\DATABASES\AvgClaimCo\15.xlsb`.[Apr-Jun$]

UNION ALL 
SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Jul-Sep$]

UNION ALL 
SELECT * FROM`F:\DATABASES\AvgClaimCo\15.xlsb`.[Oct-Dec$]

[Aflatoon]

Have you tried using Power Query instead to retrieve the data to your model?

[nawaf]

would that be fast? if so, no I didn't try, dunno how. is there a site that can teach me?