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