Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
Solved! Go to 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
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.
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.
@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.
@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 🙂
⭕ 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).
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
15 | |
14 | |
12 |