Forum Discussion

Sonashish's avatar
Sonashish
Resolver I
5 years ago
Solved

Create excel report

Hello  I want to create a new report, getting data from many Excel files saved on SharePoint online and upload it and also want refresh data directly online.   Currently there are 10 reports exist...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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 :

    https://blog.crossjoin.co.uk/2020/04/06/understanding-the-the-key-didnt-match-any-rows-in-the-table-error-in-power-query-in-power-bi-or-excel/

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

  • Sonashish's avatar
    Sonashish
    5 years ago

    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

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

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,
Kelly

Did I answer your question? Mark my post as a solution!

 

Sonashish's avatar
Sonashish
Resolver I
5 years ago

Hello Kelly,

 

Yes folder is hosted in SharePoint. I followed below steps

  1. Get Data Source
  2. Select SharePoint Holder
  3. Enter SharePoint URL
  4. I am getting same type of image1 as you mentioned above
  5. But I am not getting Combine+Edit. I am getting Combine+Load or Combine+Transform ( I tried both)
  6. I selected Combine+Load, because If I select Combine+Transforrm, it is throwing error because folder containing other type of file.
  7. After selecting Combine+Load,  then I can see only excel files
  8. 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