Unable to connect to Excel on Sharepoint using Web connector

[PrairieWalker]

I need to connect my PowerBi to an excel file housed on sharepoint.  I'm following instructions to open the file in app, use the "Copy Path", take off the ending, and pasting URL into get data via Web option.  I keep getting this error.

Feedback Type:
Frown (Error)

Timestamp:
2022-03-07T19:32:54.5914406Z

Local Time:
2022-03-07T13:32:54.5914406-06:00

Session ID:
a8be8c9f-1664-49b6-b0b6-f7d08dc5678c

Release:
November 2021

Product Version:
2.99.862.0 (21.11) (x64)

Stack Trace:
Microsoft.Mashup.Client.UI.Shared.ExceptionResultException
Microsoft.Mashup.Evaluator.Interface.ErrorException: Specified value has invalid CRLF characters.
Parameter name: value ---> System.ArgumentException: Specified value has invalid CRLF characters.
Parameter name: value ---> System.ArgumentException: Specified value has invalid CRLF characters.
Parameter name: value
at System.Net.WebHeaderCollection.CheckBadChars(String name, Boolean isHeaderValue)
at System.Net.WebHeaderCollection.Set(String name, String value)
at System.Net.WebHeaderCollection.set_Item(HttpRequestHeader header, String value)
at Microsoft.Mashup.Engine1.Library.Http.HttpResourceCredentialDispatcher.WebRequestCredentialDispatcher.Apply(OAuthCredential credential)
at Microsoft.Mashup.Engine1.Library.Http.HttpServices.ApplyCredentials(MashupHttpWebRequest httpRequest, String resourceKind, Uri requestUri, ResourceCredentialCollection credentials, IEngineHost hostEnvironment, String oAuthResource)
at Microsoft.Mashup.Engine1.Library.Web.HttpRequest.ApplyCredentialsToRequest(WebRequest webRequest, ResourceCredentialCollection credentials)
at Microsoft.Mashup.Engine1.Library.Web.Request.CreateWebRequest(Uri uri, ResourceCredentialCollection credentials)
at Microsoft.Mashup.Engine1.Library.Web.Request.CreateResponse(ResourceCredentialCollection credentials)
at Microsoft.Mashup.Engine1.Library.Web.Request.GetResponseCore(ResourceCredentialCollection credentials)
at Microsoft.Mashup.Engine1.Library.Common.RetryPolicy.Execute[TResult](IEngineHost host, Func`1 func)
at Microsoft.Mashup.Engine1.Library.Web.Request.GetResponse(ResourceCredentialCollection credentials, RetryPolicy retryPolicy, SecurityExceptionCreator securityExceptionCreator, Boolean tokenRefreshed)
at Microsoft.Mashup.Engine1.Library.Web.WebContentsBinaryValue.GetResponse(Request request)
at Microsoft.Mashup.Engine1.Library.Web.WebContentsBinaryValue.Open()
at Microsoft.Mashup.Engine1.PreviewInference.GetBytes(BinaryValue value, Int32 count)
at Microsoft.Mashup.Engine1.PreviewInference.Apply(BinaryValue value, Char[] candidateDelimiters, QuoteStyle[] candidateQuoteStyles, Boolean inferUtf8)
at Microsoft.Mashup.Engine1.PreviewInference.Apply(Value value, Char[] candidateDelimiters, QuoteStyle[] candidateQuoteStyles, Boolean inferUtf8)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.SerializePreviewValue(IEngine engine, IValue value, Action`2 rowCountCallback, Nullable`1 options)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.NonTableValuePreviewValueSource.get_Value()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass0_0.<RunStub>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass14_0.<TryReportException>b__1()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPreviewValueSource)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass12_1`1.<OnBeginGetResult>b__0()
at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.EvaluationHost.Run()
at Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
at Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
at Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_Value()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_Value()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.99.862.0","ModuleName":"","Component":"Microsoft.Mashup.Engine1.Library.Http.HttpResourceCredentialDispatcher.WebRequestCredentialDispatcher","Error":"Microsoft.Mashup.Client.UI.Shared.ExceptionResultException","MethodDef":"Apply","ErrorOffset":""}

OS Version:
Microsoft Windows NT 10.0.19043.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528372]

Peak Virtual Memory:
103 GB

Private Memory:
545 MB

Peak Working Set:
838 MB

IE Version:
11.789.19041.0

User ID:
8478e961-18e2-4f1f-8943-7864f2c4bf2a

Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
Empty

Model Version:
PowerBI_V1

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_dynamicParameters
PBI_rdlNativeVisual

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
PBI_compositeModelsOverAS
PBI_enhancedTooltips
PBI_enableWebView2
PQ_WebView2Connector
PBI_useModernFormatPane
PBI_scorecardVisual

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
125%

Supported Services:
Power BI

[v-easonf-msft]

Hi @PrairieWalker  

I found a similar issue that has been solved , you can refer to it .

Issue when import database from sharepoint - Microsoft Power BI Community

Best Regards,
Community Support Team _ Eason

[croberts21]

I'm having the same problem. Nothing I try will work to get PBI to read an Excel file on our Sharepoint site.

I've tried every permutation I can think of to connect PBI to an Excel file on our Sharepoint site. I've used the Web connector, and Sharepoint folder connector. I put in just our Sharepoint site URL which is like Myorg.sharepoint.com. I've watched at least 3 video tutorials and found at least 5 pages on how to do this and nothing works. I've tried various combinations of my credentials, including my MS Office creds (which works to sign into PBI), and nothing works.

The error usually is:

Microsoft.Mashup.Engine1.Library.Resources.HttpResoure: Request failed: OData version: 3 and 4, Error: The remote server returned an error: (500) Internal Server error. (500 INTERNAL SERVER ERROR). OData Version: 4, Error: The remote server returned an error: (500) Internal Server error. (500 INTERNAL SERVER ERROR). OData version: 3, Error: The remote server returned an error: (500) Internal Server error. (500 INTERNAL SERVER ERROR) "

but is sometimes it's this: "We're unable to connect. Please delete your credentials and try again later."

Do we need to set up our Sharepoint site to accept incoming connections to PBI? Or set up something else?