Error expanding "RequiredAttendees" table from Exchange Online

[James-PBI]

We are using Microsoft Bookings to schedule appointments. I want to import this data into Power Query to see appointment dates, subjects, and attendees.

Since Microsoft Bookings uses an Exchange account, I am loading the data into Power Query using the Exchange Online connector:

Then, I navigate to the Calendar:

When I try to expand the "RequiredAttendees" table, I get this error:

Here is the full error message text:

Unexpected error: The type of the object in the store (Booking) does not match that of the local object (CalendarItem).
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: The type of the object in the store (Booking) does not match that of the local object (CalendarItem). ---> Microsoft.Exchange.WebServices.Data.ServiceLocalException: The type of the object in the store (Booking) does not match that of the local object (CalendarItem). ---> Microsoft.Exchange.WebServices.Data.ServiceLocalException: The type of the object in the store (Booking) does not match that of the local object (CalendarItem).
at Microsoft.Exchange.WebServices.Data.EwsServiceXmlReader.ReadServiceObjectsCollectionFromXml[TServiceObject](XmlNamespace collectionXmlNamespace, String collectionXmlElementName, GetObjectInstanceDelegate`1 getObjectInstanceDelegate, Boolean clearPropertyBag, PropertySet requestedPropertySet, Boolean summaryPropertiesOnly)
at Microsoft.Exchange.WebServices.Data.GetItemResponse.ReadElementsFromXml(EwsServiceXmlReader reader)
at Microsoft.Exchange.WebServices.Data.ServiceResponse.LoadFromXml(EwsServiceXmlReader reader, String xmlElementName)
at Microsoft.Exchange.WebServices.Data.MultiResponseServiceRequest`1.ParseResponse(EwsServiceXmlReader reader)
at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.ReadResponse(EwsServiceXmlReader ewsXmlReader)
at Microsoft.Exchange.WebServices.Data.SimpleServiceRequestBase.ReadResponseXml(Stream responseStream)
at Microsoft.Exchange.WebServices.Data.SimpleServiceRequestBase.ReadResponse(IEwsHttpWebResponse response)
at Microsoft.Exchange.WebServices.Data.MultiResponseServiceRequest`1.Execute()
at Microsoft.Mashup.Engine1.Library.Http.RetryPolicy.Execute[TResult](IEngineHost host, Func`1 func)
at Microsoft.Mashup.Engine1.Library.Exchange.ExchangeTracingService.LoadPropertiesForItems(PropertySet allPropertySet, FindItemsResults`1 items)
at Microsoft.Mashup.Engine1.Library.Exchange.EngineExchangeService.FindItems(FolderId folderId, ItemView itemView, SearchFilter searchFilter, String folderPath, ExchangeColumnInfo[] columnInfos, HashSet`1 additionalProperties, Boolean& moreAvailable, Nullable`1& nextPageOffset)
at Microsoft.Mashup.Engine1.Library.Exchange.ExchangeCachingService.FindItems(FolderId folderId, ItemView itemView, SearchFilter searchFilter, String folderPath, ExchangeColumnInfo[] columnInfos, HashSet`1 additionalProperties, Boolean& moreAvailable, Nullable`1& nextPageOffset)
at Microsoft.Mashup.Engine1.Library.Exchange.ExchangeTableValue.ExchangeEnumerator.<GetFolderItems>d__6.MoveNext()
at Microsoft.Mashup.Engine1.Library.Exchange.ExchangeTableValue.ExchangeEnumerator.<GetAllItems>d__5.MoveNext()
at Microsoft.Mashup.Engine1.Runtime.SkipTakeEnumerator`1.MoveNext()
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__DisplayClass17_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.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_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)

The same error appears if I try to expand any other tables in the data set, such as "OptionalAttendees", "Categories", "Attachments", "Attributes", "Body".

After hours of research, I could only find one similar issue documented here. According to that post, Power BI uses a legacy API for Exchange Online that does not support the booking object:

It would be very frustrating to accept that as the answer. These are both Microsoft products; they should work with each other. It's confusing since Power Query is able to import most of the calendar data, but only throws the error when expanding some of the tables.

• Has anyone else had this issue?
• Any advice with troubleshooting the error message?
• Any ideas on alternative approaches?

Currently the only alternative I know of is to manually download the data from Microsoft Bookings, which has its own set of issues:

• Manually downloading the data every day is not scalable
• Appointments that are booked in the future are often rescheduled, meaning you need to overwrite that data each time it is downloaded. Since Microsoft Bookings only allows you to download the last 120 days of data, you can't simply replace the file each time. You need to keep appending the data, while overwriting the appointments that took place in the future at the time of the last download.

Thank you in advance for any insight!

[GeekAlfPro]

Hi
i face the same issue when i want to get the meetings in calendar

I agree it's very frustrating.

i managed to developp the mails, but it's not rugged/robust enough because of changes in client appointment dates.

il have all the requests and not only the last one..

anyway if Microsoft could fix the problem....

[James-PBI]

Hi there.

Glad I'm not the only one.

My current workaround was to use Power Automate. I have 2 automated flows that run every day. One adds new rows of data for any bookings with yesterday's date:

The expressions in step 2 for "formatDateTime" are:

formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd').

I only add bookings that have yesterday's date because future bookings can still be cancelled or moved. The second Power Autmote flow is connected to a second table and addresses this problem:

This is similar to the first flow, except it deletes all rows in the table first, then adds all of the bookings with a date >= today.

Once you have these flows setup, you will have 2 tables: one with all historical bookings, and another will all future bookings. Then you can get fancy with Power Query/Power BI to append and transform the data. To get the attendee's email addresses, I had to extract it from the meeting "body" field.

I hope this helps.

It's unfortunate that we need to build such complex workarounds because Microsoft hasn't updated an old Exchange connector.

[artemus]

This is a feature gap in the Exchange connector. If you would like to see it addressed you can upvote the related issue: Exchange Online Web API's throwing errors - Microsoft Power BI Community