Combine Binaries just seems unworkable with multiple large Excel files

[abertolett]

I'm trying to get Excel files from a folder and then merge them *after* removing about 100 columns I don't need.

Even trying this on just two files is excruciatingly slow. I'm doing the column removal in the sample file. 

Was wondering if there was another way to approach this.

[v-jiascu-msft]

Hi @abertolett,

Maybe you can try Python or R script. But it couldn't be helpful if you have really big datasets. Can you share a sample?

Best Regards,

Dale

[abertolett]

Thanks.

Is there any way to modify which Excel sheets are brought into the model *before* doing Combine Files?

It takes forever for Power BI to finish "connecting to Excel".

I'm thinking everything is going to have to be csv going forward.

[v-jiascu-msft]

Hi @abertolett,

There is a workaround.

1. Import a small Excel workbook.

2. Open Advanced Editor in the Query Editor and replace the values 2, 3 in the snapshot with values of the big Excel workbook.

3. Refresh Power BI Desktop.

Best Regards,

Dale

[Greg_Deckler]

How many columns are you keeping? Might be faster to keep columns instead of remove columns for that many columns. Would also be less prone to error in the event that a column name changes.