Exchange Online Web API's throwing errors

rdoherty 

That seems to work fine for me. Check code in the query editor and recreate it if necessary.

I was able to repro using a new model and recreating theq query from scratch.

Here is the full query:

let
Source = Exchange.Contents("[email protected]"),
Calendar1 = Source{[Name="Calendar"]}[Data],
#"Expanded Attributes" = Table.ExpandRecordColumn(Calendar1, "Attributes", {"AppointmentType"}, {"Attributes.AppointmentType"}),
#"Filtered EndsAfter2019" = Table.SelectRows(#"Expanded Attributes", each [Start] > #datetime(2019, 12, 31, 0, 0, 0)),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered EndsAfter2019", "Start", "Start - Copy"),
#"Extracted Date" = Table.TransformColumns(#"Duplicated Column",{{"Start - Copy", DateTime.Date, type date}}),
#"Sorted Rows" = Table.Sort(#"Extracted Date",{{"Start", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "DurationHours", each Duration.TotalHours([End] - [Start])),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"DurationHours", type number}}),
#"Filtered AppointmentType" = Table.SelectRows(#"Changed Type", each ([IsAllDayEvent] = false) and ([Attributes.AppointmentType] = "Single")),
#"Extracted Values" = Table.TransformColumns(#"Filtered AppointmentType", {"Categories", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Values", {{"Categories", each Text.BeforeDelimiter(_, ";"), type text}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Text Before Delimiter", "Subject", "Subject - Copy"),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Duplicated Column1", {{"Subject - Copy", each Text.BetweenDelimiters(_, "{", "}"), type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Extracted Text Between Delimiters",{{"Subject - Copy", Text.Trim, type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Folder Path", "Location", "DisplayTo", "DisplayCc", "RequiredAttendees", "OptionalAttendees", "IsAllDayEvent", "LegacyFreeBusyStatus", "IsReminderSet", "ReminderMinutesBeforeStart", "Importance", "HasAttachments", "Attachments", "Preview", "Attributes.AppointmentType", "Body"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Start - Copy", "Subject", "Start", "End", "DurationHours", "Categories", "Subject - Copy", "Id"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Start - Copy", "Date"}, {"Categories", "Category"}, {"Subject - Copy", "EngagementId"}})
in
#"Renamed Columns"

The specific step that is generating the error, which worked previously, is this step:

#"Extracted Values" = Table.TransformColumns(#"Filtered AppointmentType", {"Categories", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

Note, you need to have single appointments with categories to repro this.

Here is an even simpler repro:

let
Source = Exchange.Contents("[email protected]"),
Calendar1 = Source{[Name="Calendar"]}[Data],
#"Filtered Rows" = Table.SelectRows(Calendar1, each [Start] > #datetime(2019, 12, 31, 0, 0, 0)),
#"Extracted Values" = Table.TransformColumns(#"Filtered Rows", {"Categories", each Text.Combine(List.Transform(_, Text.From), ";"), type text})
in
#"Extracted Values"

rdoherty 

I tried the new version again and it works as normal. Besides, you may try Error handling.

v-chuncz-msft I'm also running the latest version of PowerBI desktop, and the API is still failing:

Feedback Type:
Frown (Error)

Timestamp:
2021-10-27T13:41:29.2730916Z

Local Time:
2021-10-27T09:41:29.2730916-04:00

Session ID:
f0530a04-d9b2-43c4-85f2-24fb19c77b11

Release:
October 2021

Product Version:
2.98.882.0 (21.10) (x64)

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

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

Peak Virtual Memory:
103 GB

Private Memory:
751 MB

Peak Working Set:
986 MB

IE Version:
11.1.22000.0

User ID:
15261921-eeae-4b73-a1e0-c99f872d8240

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

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\rdoherty\Microsoft\Power BI Desktop Store App\FrownSnapShotcd3eb798-fb97-485b-9b23-c7a2fb2c6bc1.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\rdoherty\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_rdlNativeVisual

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_dynamicParameters
PBI_enhancedTooltips

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
MSIT

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared SingleAppointments = let
Source = Exchange.Contents("[email protected]"),
Calendar1 = Source{[Name="Calendar"]}[Data],
#"Expanded Attributes" = Table.ExpandRecordColumn(Calendar1, "Attributes", {"AppointmentType"}, {"Attributes.AppointmentType"}),
#"Filtered EndsAfter2019" = Table.SelectRows(#"Expanded Attributes", each [Start] > #datetime(2019, 12, 31, 0, 0, 0)),
#"Duplicated Column" = Table.DuplicateColumn(#"Filtered EndsAfter2019", "Start", "Start - Copy"),
#"Extracted Date" = Table.TransformColumns(#"Duplicated Column",{{"Start - Copy", DateTime.Date, type date}}),
#"Sorted Rows" = Table.Sort(#"Extracted Date",{{"Start", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "DurationHours", each Duration.TotalHours([End] - [Start])),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"DurationHours", type number}}),
#"Filtered AppointmentType" = Table.SelectRows(#"Changed Type", each ([IsAllDayEvent] = false) and ([Attributes.AppointmentType] = "Single")),
#"Extracted Values" = Table.TransformColumns(#"Filtered AppointmentType", {"Categories", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Values", {{"Categories", each Text.BeforeDelimiter(_, ";"), type text}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Text Before Delimiter", "Subject", "Subject - Copy"),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Duplicated Column1", {{"Subject - Copy", each Text.BetweenDelimiters(_, "{", "}"), type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Extracted Text Between Delimiters",{{"Subject - Copy", Text.Trim, type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Folder Path", "Location", "DisplayTo", "DisplayCc", "RequiredAttendees", "OptionalAttendees", "IsAllDayEvent", "LegacyFreeBusyStatus", "IsReminderSet", "ReminderMinutesBeforeStart", "Importance", "HasAttachments", "Attachments", "Preview", "Attributes.AppointmentType", "Body"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Start - Copy", "Subject", "Start", "End", "DurationHours", "Categories", "Subject - Copy", "Id"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Start - Copy", "Date"}, {"Categories", "Category"}, {"Subject - Copy", "EngagementId"}})
in
#"Renamed Columns";

shared MapGCEToOutlook = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZHBagJBDIZfJezZPsS6CL2IUkUPi4cwG9bgTEYyWWXfvqnFnpx6C/n+5OdP+r7ZTSFQKbCSEUdKJNYsXjVPi77ZK7KwjMUlz/oXcCJo7zjDxs6kjj9z5AHnCj1gQOMsD9zllCZhm+GY9eL0r1GZbofk1sXUd9yoIloTRlgq4aUi2BMmWBPZM8QDQDcVy8mLNvh2NqaftNuI8k/aLWnJgrFqFc7wRTiw+FXfmyl9FIz0XrmbrtesVrFdZvMZ6FhDrJ1pdfPnutHpGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GCECategoryName = _t, OutookCategoryName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"GCECategoryName", type text}, {"OutookCategoryName", type text}})
in
#"Changed Type";

shared Accounts = let
Source = OData.Feed("https://microsoftsales.api.crm.dynamics.com/api/data/v9.2/", null, [Implementation="2.0"]),
accounts_table = Source{[Name="accounts",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(accounts_table,{"accountnumber", "websiteurl", "name", "accountid"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [accountnumber] = "1+78XD+34217" or [accountnumber] = "1-G18PUO" or [accountnumber] = "1-3LT6RD" or [accountnumber] = "11-1A0J4" or [accountnumber] = "1-SRKNO7" or [accountnumber] = "1-YMBRXN" or [accountnumber] = "1+78XD+8850" or [accountnumber] = "1-DHAT47"),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"accountid", "AccountGuid"}, {"accountnumber", "AccountId"}, {"websiteurl", "AccountURL"}, {"name", "AccountName"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"AccountName", Order.Ascending}})
in
#"Sorted Rows";

shared Engagements = let
Source = OData.Feed("https://microsoftsales.api.crm.dynamics.com/api/data/v9.2/", null, [Implementation="2.0"]),
msp_engagements_table = Source{[Name="msp_engagements",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(msp_engagements_table,{"_msp_accountid_value", "msp_engagementnumber", "msp_name"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [_msp_accountid_value] = "6b3164ce-2848-469a-877f-0460ff6d613b" or [_msp_accountid_value] = "a5e84f14-4269-4c5f-9c9f-475ab4df5e69" or [_msp_accountid_value] = "7783c7d5-2055-4b28-bd0e-5d6c7cc97709" or [_msp_accountid_value] = "41e609d6-91a3-41c3-bf7f-e7d02fdd7c6f" or [_msp_accountid_value] = "154b6b1e-539a-411d-9cb6-4ae711b39db7" or [_msp_accountid_value] = "d057f372-b359-4abd-b225-2cf712d8f6b7" or [_msp_accountid_value] = "cbf9fe55-7c0d-4789-95c5-0deb991bd710"),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"_msp_accountid_value", "AccountGuid"}, {"msp_engagementnumber", "EngagementId"}, {"msp_name", "EngagementName"}})
in
#"Renamed Columns";

v-chuncz-msft this error is due to PowerBI using a legacy API for Exchange Online that does not support the Booking object. The only objects that are supported by this legacy API are listed here:

https://docs.microsoft.com/en-us/exchange/client-developer/web-service-reference/ews-xml-elements-in-exchange 

So if your calendar contains objects that are not supported in this legacy API calls to Exchange Online will fail. The workaround is to delete any items from the calendar that are not supported by the legacy API.

I went in and removed the Bookings from my calendar and I no longer got the error. So unfortunately I cannot use the new Exchange Online bookings feature which is documented here:

https://docs.microsoft.com/en-us/microsoft-365/bookings/bookings-overview?view=o365-worldwide

See Microsoft internal IcM 269505789 for more details.

What is the best way to submit feedback to the PowerBI team to ask them to update the legacy API they are using for Exchange Online so that Power BI can support the newer objects like bookings?

Same experience here today in my PowerBI report on my calendar that I'm using for some years now.

Message: The type of the object in the store (Booking) does not match that of the local object (CalendarItem).

Power Query step that failed is: 

#"Extracted Values Categories" = Table.TransformColumns(#"Removed Columns", {"Categories", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

Not sure why step extracting values in Categories failed as the item is nicely categorized in my calendar. 

The same message is raised when I try to Extract Values from Attributes.

As soon as I filter the specific Bookings item in my calendar before this step, the report is fine. I have now filtered on a specific subject, which is not scalable and requires manual changes to the query when using Bookings. The information 'type of the object in the store (Booking)' that you'd want to filter on is probably in Attributes. Which you cannot get to...

Hello v-chuncz-msft, does this still have status Investigating? I'd like to continue reporting to me on my calendar as well as use Bookings with me. 

Left out some queries from below here as they're not relevant to the matter and include internal data sources.

Feedback Type:
Frown (Error)

Error Message:
The type of the object in the store (Booking) does not match that of the local object (CalendarItem).

Stack Trace:
Microsoft.Mashup.Host.Document.SerializedException
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)

Stack Trace Message:
The type of the object in the store (Booking) does not match that of the local object (CalendarItem).

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Delegate.DynamicInvokeImpl(Object[] args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_1.<Main>b__3()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.Main(String[] args)

PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.110.805.0","ModuleName":"","Component":"Microsoft.Mashup.Evaluator.EvaluationHost","Error":"Microsoft.Mashup.Host.Document.SerializedException - Microsoft.Mashup.Host.Document.Evaluation.ErrorEvaluationResult","MethodDef":"OnException","ErrorOffset":""}

Snapshot Trace Logs:
C:\Users\XXXXX\Microsoft\Power BI Desktop Store App\FrownSnapShot319dd994-1bf3-4e9f-a9ff-25e815603fa2.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\XXXXX\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_azureMapVisual
PBI_enableWebView2
PQ_WebView2Connector
PBI_sparklines

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_compositeModelsOverAS
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_horizontalFusion
PBI_relationshipEditPane

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
MSIT

PowerBINonFatalError_ErrorDescription:
Microsoft.Mashup.Host.Document.Evaluation.ErrorEvaluationResult

PowerBIUserFeedbackServices_IsReported:
True

Formulas:

section Section1;

shared calendar = let
Source = Exchange.Contents(email),
Calendar1 = Source{[Name="Calendar"]}[Data],
#"Filtered Rows" = Table.SelectRows(Calendar1, each [Folder Path] = "\Calendar\" and [Start] >= #"start report FY" and [Start] <= #"end report FY"),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"RequiredAttendees", "OptionalAttendees", "Preview", "DisplayTo", "DisplayCc", "IsAllDayEvent", "ReminderMinutesBeforeStart", "Importance", "HasAttachments", "Attachments", "Body", "Id", "IsReminderSet"}),
#"Extracted Values Categories" = Table.TransformColumns(#"Removed Columns", {"Categories", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Values Categories",{{"Categories", "epic"}, {"LegacyFreeBusyStatus", "show as"}, {"Start", "date | date start"}, {"Subject", "subject"}, {"End", "date | date end"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each [epic] <> "" and [epic] <> "Green Category" and [subject] <> "Focus time" and [epic] <> "Holiday"),
#"Replaced Value2" = Table.ReplaceValue(#"Filtered Rows1",";Business","",Replacer.ReplaceText,{"epic"}),
#"Added accreditation" = Table.AddColumn(#"Replaced Value2", "accreditation", each Text.AfterDelimiter([Location], "accreditation | "), Text.Type),
#"Added certification" = Table.AddColumn(#"Added accreditation", "certification", each Text.AfterDelimiter([Location], "certification | "), Text.Type),
#"Expanded Attributes MyResponseType" = Table.ExpandRecordColumn(#"Added certification", "Attributes", {"MyResponseType"}, {"Attributes.MyResponseType"}),
#"Inserted Year" = Table.AddColumn(#"Expanded Attributes MyResponseType", "date | CY", each Date.Year([#"date | date start"]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "date | date start month", each Date.MonthName([#"date | date start"]), Text.Type),
#"Inserted Month Number" = Table.AddColumn(#"Inserted Month Name", "date | date start month nr", each Date.Month([#"date | date start"]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Month Number", "date | date start day", each Date.DayOfWeekName([#"date | date start"]), Text.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "date | date start day nr", each Date.DayOfWeek([#"date | date start"], Day.Monday), Int64.Type),
#"Inserted Hour" = Table.AddColumn(#"Inserted Day of Week", "date | date start hour", each Time.Hour([#"date | date start"]), Int64.Type),
#"Replaced Value OOF by OOO" = Table.ReplaceValue(#"Inserted Hour","OOF","OOO",Replacer.ReplaceText,{"show as"})
in
#"Replaced Value OOF by OOO";

shared #"dim | accreditation" = let
Source = calendar,
#"Removed Other Columns" = Table.SelectColumns(Source,{"accreditation"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows";

shared #"dim | certification" = let
Source = calendar,
#"Removed Other Columns" = Table.SelectColumns(Source,{"certification"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows";

shared #"dim | epic" = let
Source = calendar,
#"Removed Other Columns" = Table.SelectColumns(Source,{"epic"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows";

[ Description = "leave parameter at default to report on current FY" ]
shared #"start report FY" = #datetime(2022, 7, 1, 0, 0, 0) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true];

[ Description = "leave parameter at default to report on current FY" ]
shared #"end report FY" = #datetime(2023, 6, 30, 23, 59, 59) meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true];

[ Description = "fill your emailaddress to report on your Exchange Calendar" ]shared email = "[email protected]" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true];

shared #"date | refresh date" = let
Source = DateTime.FixedLocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "date | refresh date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"date | refresh date", type datetime}})
in
#"Changed Type";