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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
GeorgeGokmen
Helper I
Helper I

How can I get the data from excel files from a folder for a Power BI Query?

Hi Everyone, 

 

When I specify a path of a folder with excel files for my data source, Power BI only shows the details of the files in the folder instead of the data tables in the excel files. When I click on Transform and Combine then select Sheet1 under the parameters, Power BI shows only the first row instead of the entire table.

 

How can I connect to a folder that contains the Excel files as a data source in PowerBI? 

2 ACCEPTED SOLUTIONS

Hello @GeorgeGokmen 

 

you can click on the double arrows on the content-column (this is the content of the Excel-files). Power BI opens an assistant to choose your sheet name. Afterwards Power BI creates a custom function to read from every file (uses all the time the same sheet name).

Jimmy801_0-1606284871545.png

After that you can go to the query "Transform Sample File" to change the steps applied to every file (here you can choose promote headers, or whatever you want to apply to every the choosen sheet-name of every file

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

View solution in original post

V-pazhen-msft
Community Support
Community Support

@GeorgeGokmen 

You cannot combine multiple excel files with different schema. For example, you can combine the first two files but not with third file.

V-pazhen-msft_0-1606454575004.png V-pazhen-msft_1-1606454589573.pngV-pazhen-msft_3-1606454666246.png

The combined result is following, if you use the first file of combination. 

V-pazhen-msft_4-1606454852433.png

 

With different schema, you can only import excel files separately into power bi desktop, then do further transformation. 


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
GeorgeGokmen
Helper I
Helper I

Thanks to everyone who provided a solution. I was able to overcome the "issue" by opening the excel files and "enabling editing" and saving and then closing the file. After these steps, the Power BI query started importing all the rows in the excel files that are stored in the source folder. 

V-pazhen-msft
Community Support
Community Support

@GeorgeGokmen 

You cannot combine multiple excel files with different schema. For example, you can combine the first two files but not with third file.

V-pazhen-msft_0-1606454575004.png V-pazhen-msft_1-1606454589573.pngV-pazhen-msft_3-1606454666246.png

The combined result is following, if you use the first file of combination. 

V-pazhen-msft_4-1606454852433.png

 

With different schema, you can only import excel files separately into power bi desktop, then do further transformation. 


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Fowmy
Super User
Super User

@GeorgeGokmen 

Connect to the folder and click Transform instead of Combine: https://docs.microsoft.com/en-us/power-query/connectors/folder#:~:text=To%20connect%20to%20a%20folde....

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Fowmy, Power BI still shows "Date accessed, date created, date modified and etc." as table headers after clicking Transform. I still cannot load and transform the actual data that sits on sheet1 on every excel file that is placed in the folder. 

 

As I check the available tutorials online, I realize Microsoft removed the "Edit" feature before Transform and Load the data. 

 

When I click on Transform and Combine, the available sheet only shows the first row of the data, just 1 one row at the top. How can I expand it to list all the rows? 

Hello @GeorgeGokmen 

 

you can click on the double arrows on the content-column (this is the content of the Excel-files). Power BI opens an assistant to choose your sheet name. Afterwards Power BI creates a custom function to read from every file (uses all the time the same sheet name).

Jimmy801_0-1606284871545.png

After that you can go to the query "Transform Sample File" to change the steps applied to every file (here you can choose promote headers, or whatever you want to apply to every the choosen sheet-name of every file

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.