Forum Discussion
Create excel report
Hi Sonashish ,
Based on my test,as you have different sheet names for each .xlsx file,which causes the error below:
If you have different sheet names,it would be hard for power query to identify the row in the table that contains the data you want to see.That is why you see the error.The only solution is to modify all the sheet names to the same one,such as "Sheet 1":
Source{[Item="Sheet1",Kind="Sheet"]}[Data]
Below blog has detailed explanation you may refer to :
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hello Kelly,
That is what I mentioned that if you are combining excel files, so all the excel file sheet name should be same, surprisibgly it case sensitive as well. So in all excel sheet name should be in same case.
I saw the blog you mentioned, but it should also mention in Microsoft site as well.
Thanks for confirmation.
Sona
Hello Kelly,
Thanks for URL.
I tried the steps mentioned in URL. But I am not able to combine the excels from SharePoint even not from local machine folder. It shows the excel names after connecting from SharePoint or local folder. But whenever I try to click on Combine+Load or Combine+Transform. It load only one table data, second table data are not loading, I already spent lot of time. However I save these excel's as CSV files and thne load or transform in both case it works. It looks like it is some excel formatting issue or something related with excel.
If you have some repository, please let me know I will upload the excel for for your reference. You can try and let me know what I am missing.
Regards
Sonashish
Hi Sonashish ,
When you are connecting from local machine folder,you will see:
After selecting combine+edit,you will see one table data in the display window,but notice that on the left top conner ,you can select the other one you wanna combine.You can also check the below vedio,which has details steps about how to connect local excel fold with power bi :
https://www.youtube.com/watch?v=9sfCDCpWTfc
If you wanna connect excel folders hosted on sharepoint or sharepoint online,you may check the below vedio,it also has detailed steps inside,and will advise you which step should not be taken.
https://www.youtube.com/watch?v=XuLnSYjmsJo&feature=youtu.be
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hello Kelly,
Yes folder is hosted in SharePoint. I followed below steps
- Get Data Source
- Select SharePoint Holder
- Enter SharePoint URL
- I am getting same type of image1 as you mentioned above
- But I am not getting Combine+Edit. I am getting Combine+Load or Combine+Transform ( I tried both)
- I selected Combine+Load, because If I select Combine+Transforrm, it is throwing error because folder containing other type of file.
- After selecting Combine+Load, then I can see only excel files
- Then I select Combine, but it is showing all the excel, but pulling the data only for first table.
I am not sure what I am missing?
SUrprisingly when I save these excel file as CSV files and upload in different SharePOnt folder, it works correctly. I dont know where is the problem, but I think some problem in Excel.
Please advise how to check these excel files, if you want to I can share you some of sample excel files for your testing..
Please advise
Sonashish
Hi Sonashish ,
I find a reply from below post which may explain how your issue occurs:
https://powerbi.tips/2016/08/load-multiple-excel-xlsx-files/
# reply:
In this example we are loading multiple files that have the exact same data structure inside. The query editor is really good at loading data that is similar in column structure… but, it is not good at loading many files of different data structures, or multiple columns. The query editor is intended to load one type of data (multiple excel, csv files, SQL query… etc.) After the file(s) have been loaded the Query Editor allows for data transformation to prepare the data for visualizations. Each data source should be unique with different data transformation steps.
If I read your question correctly, you’d like to load a bunch of files of all different data structures, such as one file with Sales by month, Another with a different data such as product categories. I would not recommend loading all of the information as one table. Rather, I would suggest to make things easier you can load all the files in the folder using Get Data, from the home ribbon, select Folder, click Connect to load the files from a folder.
As tested here,although I can see all the files,csv and xls,but when I select “combine”,I will see that gibberish returns from excel file:
My conclusion is :It is not supported to combine different types of files at one time,but you could load them twice then using Append to realize it.
1.Get file>folder>Transform data:
2. Then in query editor>select .csv>click "combine file" button>"OK";
3.Repeat the same steps to load xls file;
4.After all files are loaded,select append to combine all of them:
Finally you will see all files are combined in one table:
Best Regards,
KellyDid I answer your question? Mark my post as a solution!