Forum Discussion
Power Query Excel.Workbook Function - using SharePoint file and path in parameters
Based on the standard parameters, for the data access function Excel.Workbook, I believe a set of queries I have inherited has been corrupted but it is not being picked up by Syntax check or loading background query.
The parameters within Excel.Workbook() seem to either be an advanced query to a file and path, or someone has pasted in rubbish to the query - any advice? The query appears exactly as shown below, deidentified names, including the whitespace where you would expect dots for the site url and file name...
```
#"Imported Excel Workbook" = Excel.Workbook(#"Example_File_Name xlsx_https://exampleorg sharepoint com/sites/ExamplePath/"),
Data_Sheet = #"Imported Excel Workbook"{[Item="Data",Kind="Sheet"]}[Data],
```
Somehow the query was still loading and returning rows...
I've successful rolled back the query now by changing the line to:
= Excel.Workbook(Example_File-Name, null, true),
Problem solving has led to answer, so apologies for the seemingly null and void question, but I just want to make sure I haven't missed anything about a means of using the function that I am missing.
Hi, Paulus05 ;
For excel files storing in Sharepoint, we will use the Sharepoint Folder connector to connect to it. Based on my test, I can use it successfully when using the method provided in https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/, please check your Site URL, it will be in the format like below:
https://****.sharepoint.com/sites/****
Here are two method connecting to Excel file on Sharepoint, please refer to it: https://community.powerbi.com/t5/Integrations-with-Files-and/SharePoint-On-Premise-Connection-to-Excel-File/td-p/109560
Power Query SharePoint list connector - Power Query | Microsoft DocsQuick steps for connecting to SharePoint using Pow... - Microsoft Power BI Community
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi, Paulus05 ;
For excel files storing in Sharepoint, we will use the Sharepoint Folder connector to connect to it. Based on my test, I can use it successfully when using the method provided in https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/, please check your Site URL, it will be in the format like below:
https://****.sharepoint.com/sites/****
Here are two method connecting to Excel file on Sharepoint, please refer to it: https://community.powerbi.com/t5/Integrations-with-Files-and/SharePoint-On-Premise-Connection-to-Excel-File/td-p/109560
Power Query SharePoint list connector - Power Query | Microsoft DocsQuick steps for connecting to SharePoint using Pow... - Microsoft Power BI Community
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks for the information, I will add to my learning Collection.
I believe the problem in the query I was dealing with was mostly to do with random text copy and pasted from a line in a readme document someone was working on at the same time as the query.
However, good to have your information about usinng the Sharepoint Folder connector for reference, thank you 😎Hi, Paulus05 ;
Is your problem solved? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
