How to swap out data source if my data is a folder of excel sheets?

[derekli17001]

i have a power bi template with visuals where i normally just use the "data source settings" option to switch out the old excel sheet for a new one where the visuals then automatically refresh and adjust to the new sheets. However, this time, i have a folder of multiple excel sheets and i cannot combine and load them as together they exceed the maximum cells excel allows. What do i do? Assume the excel sheets all have the same column names etc

[Kishore_KVN · Accepted solution]

Hello @derekli17001 ,

In this case instead of data source settings, you have to go to transform data and it will opens up Power Query Editor and in the query options you can see a parameter which will be holding the path of the folder. There you can modify the new path of the folder. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[v-yohua-msft · Accepted solution]

Hi, @derekli17001 

Thanks for the reply from @Kishore_KVN , please allow me to provide another insight:

In Power BI Desktop, go to the Home tab, select Get Data, and then select Folders. Navigate to and connect to the folder that contains the Excel file. 

Power BI prompts you to preview the files you find. Use the Merge button at the top of the dialog box, which will launch the Merge Files wizard. 

After you merge your files, you may want to use Power Query Editor to ensure that your data is clean and structured as needed. You can remove unnecessary columns, filter rows, or make other transformations.

Once loaded into the Power BI model, the visuals should automatically refresh and adjust to the newly combined dataset. You can see more information in following link:

Combine files (binaries) in Power BI Desktop - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kishore_KVN · Accepted solution]

Hello @derekli17001 ,

In this case instead of data source settings, you have to go to transform data and it will opens up Power Query Editor and in the query options you can see a parameter which will be holding the path of the folder. There you can modify the new path of the folder. 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!