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,

 

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

Sonashish's avatar
Sonashish
Resolver I
5 years ago

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

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

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

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

    • Sonashish's avatar
      Sonashish
      Resolver I
      5 years ago

      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

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

        Hi  Sonashish ,

         

        Sorry but we only support forum,you can pack the file into a compressed package,then upload it to onedrive business and share the link with me.

         

        Best Regards,
        Kelly

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