Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
tmquery
New Member

HELP - Unexpected error: Object reference not set to an instance of an object.

I am trying to Get excel workbook data from multiple workbooks in a folder using the query below and then expand the tables of each workbook data to view the specific data elements i am targeting.

Excel.Workbook([Content],true)

I do this often and do not have issues. However the workbooks i am trying to query have a lot of defined names, objects, and tables.

What could be causing the error below (I also included images

 

Unexpected error: Object reference not set to an instance of an object.
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.TruncatedIdentifier(String s)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.WriteCustomRecordType(IRecordTypeValue recordType, AllowCustomTypes allowCustomTypes)
at Microsoft.Mashup.Evaluator.Services.ValueSerializer.FinishWriting()
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
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(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass27_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(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)
at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass12_0`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.BootstrapAppDomainManager.Execute(String[] argv)
--- 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_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

 

 

Data is there

tmquery_0-1748277592643.png

 

 

I get the error when i expand

 

tmquery_1-1748277632584.png

 

1 ACCEPTED SOLUTION
v-sdhruv
Community Support
Community Support

Hi @tmquery ,
You can follow these approaches to troubleshoot-
1.Filter Kind and Item Before Expanding

Before expanding  Data, filter to include only actual tables or worksheets and exclude problematic kinds like DefinedName or Chart:

let
Source = Folder.Files("C:\YourFolderPath"),
TransformFile = Table.AddColumn(Source, "ExcelData", each Excel.Workbook([Content], true)),
Expanded = Table.ExpandTableColumn(TransformFile, "ExcelData", {"Name", "Data", "Item", "Kind", "Hidden"}),
Filtered = Table.SelectRows(Expanded, each ([Kind] = "Table" or [Kind] = "Sheet") and [Data] <> null)
in
Filtered

or

2.Use Table.SelectedRows with Error Handling

Wrap the expansion in a  try...otherwise block to handle rows that might cause exceptions:

SafeExpand = Table.AddColumn(Filtered, "ExpandedData", each try Table.PromoteHeaders([Data]) otherwise null)

or

3.Use data not null
ValidDataOnly = Table.SelectRows(Filtered, each [Data] <> null)


Hope this helps!
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

View solution in original post

4 REPLIES 4
v-sdhruv
Community Support
Community Support

Hi @tmquery ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

v-sdhruv
Community Support
Community Support

Hi @tmquery ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

v-sdhruv
Community Support
Community Support

Hi @tmquery ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

v-sdhruv
Community Support
Community Support

Hi @tmquery ,
You can follow these approaches to troubleshoot-
1.Filter Kind and Item Before Expanding

Before expanding  Data, filter to include only actual tables or worksheets and exclude problematic kinds like DefinedName or Chart:

let
Source = Folder.Files("C:\YourFolderPath"),
TransformFile = Table.AddColumn(Source, "ExcelData", each Excel.Workbook([Content], true)),
Expanded = Table.ExpandTableColumn(TransformFile, "ExcelData", {"Name", "Data", "Item", "Kind", "Hidden"}),
Filtered = Table.SelectRows(Expanded, each ([Kind] = "Table" or [Kind] = "Sheet") and [Data] <> null)
in
Filtered

or

2.Use Table.SelectedRows with Error Handling

Wrap the expansion in a  try...otherwise block to handle rows that might cause exceptions:

SafeExpand = Table.AddColumn(Filtered, "ExpandedData", each try Table.PromoteHeaders([Data]) otherwise null)

or

3.Use data not null
ValidDataOnly = Table.SelectRows(Filtered, each [Data] <> null)


Hope this helps!
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.