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 in SharePoint Online. User are updating information in Excel Online. Currently we are dowloading the report on local machine and creating one summary report as bar chart and pie report.

 

Please advise how to achieve this, please advise.

 

Regards

Avian

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

16 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    5 years ago

    If they are all located in the same folder on SharePoint, you can create a query in Power Query to grab all the files and they will be appended together.  If they are in different folders, create a query for each report and then append them all together.

  • 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!

       

      • 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