So I know this is possible, as the one who has provided me the use case has achieved it with the same method.
You create an online excel on sharepoint and set to read for the entire organization. Then you navigate to its sharepoint file path(not the “share with” file link, the path in the sharepoint folder structure that leads to the excel) and send that to another person from their organization.
They are able to use the Web connector and the OData Flow connector to connect to the file but for me when i use those connectors and then authenticate with my organizational account login, it shows a invalid credentials error.
Has anyone had this use case and how have you guys resolved this?
From reading your question; the scenario relies on the following to produce a working query:
• SharePoint Online Library
• Site Membership, File Sharing Permissions are set appropriately
• No conflicting Policies
• Organizational Credentials are valid
• Power Query is connecting by way of Web.Content (web connect)
• Power Query is not connecting by way of OData.Feed (OData Feed connect)
• File Path is similar to the following example:
https://<yoursite>.sharepoint.com/sites/<sitename>/Shared%20Documents/Northwind.xlsx
Northwind .XLSX Customer Query retrieved from SharePoint Online
let
Source = Excel.Workbook(
Web.Contents(
"https://<yoursite>.sharepoint.com/sites/<sitename>/Shared%20Documents/Northwind.xlsx"
),
null,
true
),
Cust_Sheet = Source{[Item = "Cust", Kind = "Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(
Cust_Sheet,
[PromoteAllScalars = true]
),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{
{"CustomerID", type text},
{"Customer", type text},
{"City Name", type text},
{"PostalCode", type any},
{"Country", type text},
{"City", type text},
{"Sales Region", type text}
}
)
in
#"Changed Type"
Image of Cust Table returned from Power Query in Power BI
–
Did this answer your question? Please mark it as the solution.
Did it contribute positively towards finding the final solution? Please give it a like.
Based on my experience, this is often related to how the connection is established, especially when you’re working with read-only access. One thing to try is ensuring that you’re using the correct method for connecting—sometimes using the Web connector and the OData connector can cause authentication issues if not set up properly.
Another thing that could help is looking into more detailed guides or tutorials that explain the process step by step. For example, you can find great resources at www.myexcelonline.com that provide in-depth tutorials on how to connect SharePoint data to Power BI.
