Bug: Power Query Excel Range

[jmrstrong]

Power Query fails when an Excel Range is used as a data source and the range spans multiple Workbooks. I understand ranges should not span Workbooks so this is low priority. 

Here is an example of the Range definition 

='April 2023'!$A$1:'March 2023'!$DE$80

Here is the trace below. This is 100% repeatable. 

Unexpected error: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
Details:
Microsoft.Mashup.Client.Excel.Shim.NativeExcelException: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED)) ---> System.Runtime.InteropServices.COMException: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
--- End of inner exception stack trace ---

Server stack trace:
at Microsoft.Mashup.Client.Excel.Interop.NativeExcelInteropUtils.ValidateResult(Int32 result, Int32[] expectedValues)
at Microsoft.Mashup.Client.Excel.NativeExcelFunctionsC2RBase.Microsoft.Mashup.Client.Excel.INativeExcelFunctions.TryGetNamedObjectProperties(IntPtr workbookPointer, String name, NamedObjectProperties& namedObjectProperties)
at Microsoft.Mashup.Client.Excel.ExcelHost.<>c__DisplayClass8_0.<TryGetTable>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3_0`1.<SendAndMarshalExceptions>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass2_1.<SendAndMarshalExceptions>b__0(Object null)

Exception rethrown at [0]:
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.Mashup.Host.Document.ExceptionExtensions.CaptureAndRethrow(Exception e)
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions[T](SynchronizationContext context, Func`1 callback)
at Microsoft.Mashup.Client.Excel.ExcelHost.TryGetTable(PackageReference packageReference, String tableName, Int32 skip, Int32 take, Byte[]& content, Boolean& columnNamesGenerated, String& errorMessage)
at Microsoft.Mashup.Host.Document.Evaluation.ExcelService.TryGetTable(String name, Int32 skip, Int32 count, IDataReader& dataReader, Boolean& columnNamesGenerated, String& errorMessage)
at Microsoft.Mashup.Host.Document.Evaluation.Services.RemoteExcelServiceFactory.Stub.OnTableRequest(IMessageChannel channel, TableRequestMessage request)
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.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_TableSource()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_TableSource()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

[v-yetao1-msft]

Hi @jmrstrong 

I don't quite understand, are you using excel as the data source and then fetching data from multiple Workbooks at the same time and getting an error when loading the data to Desktop ?

Best Regards,
Community Support Team _ Ailsa Tao