Import to Power Pivot..

[dallas_lt]

Hello,
I'm exploring Power Pivot (Power Query, BI) opportunities.
I have some 24+ workbooks with IDENTICAL data structure in data sheet and 100K+ rows each. I use a pivot in each workbook.
My goal is to have one "power pivot" with all the data (+ additional small reference tables) so I can work with it faster.
What is the best way to set it up?
I wanted to use only "connections", also tried to import via Query but I get "duplicate" column names in the "power pivot" - the tables are not "appended".
Appending the data takes some space, imho..
So now I'm saving the data in .csv files and will "get data - from Folder".
Am I missing an easier way?
Thanks.
Dalius
Excel 365 MSO version 1912 64-bit.

 

[sandy666]

Use Power Query to get data from all files, don't use Pivot
transform data via Power Query Editor (Append, Merge, whatever you want) to get one table
then you can use Pivot
btw. Power Pivot increasing size of file, not Power Query

 

[dallas_lt]

Thank you, sandy666.
So, converting to .CSV is ok?

 

[sandy666]

So, converting to .CSV is ok?

if you must....
it's up to you