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

[GeorgeGokmen]

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? 

[Jimmy801 · Accepted solution]

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).

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

[GeorgeGokmen]

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 · Accepted solution]

@GeorgeGokmen 

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

 

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

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]

@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

[GeorgeGokmen]

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? 

[Jimmy801 · Accepted solution]

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).

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