Create excel report

[Sonashish]

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

[v-kelly-msft · Accepted solution]

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-...

Best Regards,
Kelly

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

[Sonashish]

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]

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]

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

[v-kelly-msft]

Hi @Sonashish ,

I find a reply from below post which may explain how your issue occurs:

https://powerbi.tips/2016/08/load-multiple-excel-xlsx-files/

# reply:

In this example we are loading multiple files that have the exact same data structure inside. The query editor is really good at loading data that is similar in column structure… but, it is not good at loading many files of different data structures, or multiple columns. The query editor is intended to load one type of data (multiple excel, csv files, SQL query… etc.) After the file(s) have been loaded the Query Editor allows for data transformation to prepare the data for visualizations. Each data source should be unique with different data transformation steps.

If I read your question correctly, you’d like to load a bunch of files of all different data structures, such as one file with Sales by month, Another with a different data such as product categories. I would not recommend loading all of the information as one table. Rather, I would suggest to make things easier you can load all the files in the folder using Get Data, from the home ribbon, select Folder, click Connect to load the files from a folder.

As tested here,although I can see all the files,csv and xls,but when I select “combine”,I will see that gibberish returns from excel file:

My conclusion is :It is not supported to combine different types of files at one time,but you could load them twice then using Append to realize it.

 1.Get file>folder>Transform data:

2. Then in query editor>select .csv>click "combine file" button>"OK";

3.Repeat the same steps to load xls file;

4.After all files are loaded,select append to combine all of them:

Finally you will see all files are combined in one table:

Best Regards,
Kelly

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

[Sonashish]

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]

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]

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]

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]

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]

I shared the link with you.

[v-kelly-msft · Accepted solution]

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-...

Best Regards,
Kelly

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

[Sonashish · Accepted solution]

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]

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!

[Sonashish]

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]

Hi  @Sonashish ,

Check the reference and similar thread below:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list

https://community.powerbi.com/t5/Service/Power-BI-Get-Data-From-SharePoint-List-Library/td-p/280109

Best Regards,
Kelly

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

[littlemojopuppy]

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.