Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
derekli17001
Helper I
Helper I

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

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

2 ACCEPTED SOLUTIONS
Kishore_KVN
Super User
Super User

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!!

View solution in original post

Anonymous
Not applicable

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.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Super User
Super User

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!!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.