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

Sonashish's avatar
Sonashish
Resolver I
5 years ago

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

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!