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 ,

 

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

  • Sonashish's avatar
    Sonashish
    Resolver I
    5 years ago

    Done. My suggestion to you that you should also write a blog for this case and publish in the forum.

    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!

  • Sonashish's avatar
    Sonashish
    Resolver I
    5 years ago

    I shared the link with you.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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
    Resolver I
    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

    There @Sonashish ,

    Yes,you are right,sorry for the misleading in the last replies,if your issue is solved,could you mark the reply as answered to let more people find the solution? 😊

    Best Regards,
    Kelly

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