I have around half a dozen Powerpivot files containing data extracted from an SQL database. Each file has around 30 million lines of data in an identical format. I do not have access to the underlying database but each powerpivot file contains the data.
I would like to connect to all of these files in one new workbook using Powerquery so that I can append them, add them to the data model and work with them in Excel.
I have found various solutions on how to get the data into CSV format using DAX studio but I would prefer to avoid this as it seems an unwieldy solution to export hundreds of millions of lines of data to CSV and then import it back to Powerquery when I already have the formatted data in Powerpivot.
I also don't have any experience of using SQL so would prefer to avoid that route.
I've tried creating a linkback as described here https://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/ but when I connect to this it only returns 1,048,576 lines of data (i.e. what Excel is limited to).
Is there an option for Powerquery to use Powerpivot data in a separate workbook as a source or another straightforward solution?
Thanks
