Forum Discussion
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?
Hello Anonymous
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
JimmyAnonymous
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.
5 Replies
- Fowmy
Super User
Anonymous
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%20folder,in%20that%20folder%20are%20displayed.________________________
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 🙂
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?
Jimmy801Community Champion
Hello Anonymous
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
Anonymous
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.
