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
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,
Kelly
Did I answer your question? Mark my post as a solution!
Hello Kelly,
Now in m SharePoint folder only xlsx files are there, but still not able to combine. If you want I can share xlsx file.
Thanks
Sona
Hello All,
Finally I found the solution, I didnt found this anywhere in any article or may I missed.
I was having 10 excel and each excel tab name is different. When I make the tabname for each excel as "sheet1". I am able to combine and transform without any issue. So more of the story is if you want to combine the excel make sure tabname should be common for all. Please correct me If I am wrong.
Thanks
Sona
Hi Sonashish ,
I didnt find the related document to support the solution,but if needed,you could share me the link of your excel files,I would test for you.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hello Kelly,
I have so many excels and they are containing client data, so unable to share thru publically. Can you send private message, I can share the link there.
Thanks
Sona