Power BI Service data refresh due to data type error + Power BI Desktop Error applying Power Query

[SeattleCrime]

Short story: this is a public report with an OData feed.

Longer story: About 6 weeks ago, the automated daily data refresh to a public Power BI report that's been live for a year stopped working. The data are coming from the public Seattle Crime Data 2008-Present source (https://data.seattle.gov/Public-Safety/SPD-Crime-Data-2008-Present/tazs-3rd5). Error details below.  No changes have occurred in the report or the Power BI data model on my end.

Trying a workaround, I chose to update the Power BI report on my desktop.   Noticing that I had not parsed the DateTime data for the report_datetime column (noticed as one of the report visuals showed an error), I parsed this to ReportDate in Power Query.  Attempting to apply the changes from Power Query results in the error you see at bottom.

Thoughts???

Power BI Service Error Details

Processing error: The following system error occurred: Type mismatch. Unable to convert a value to the data type requested for table '<pii>SPD Public Crime Reports</pii>' column '<pii>offense_start_datetime</pii>'. The data provider was unable to convert the value '<pii></pii>' from the source data type 'VT_BSTR' to the expected data type 'VT_DATE'. Please check the column data types in the data model and data source and ensure that the data types are compatible.
Cluster URI: WABI-US-EAST2-B-PRIMARY-redirect.analysis.windows.net
Activity ID: f7d8ea0f-4dc1-4077-bcea-1d6744190f19
Request ID: df8f0bb0-0f7c-799a-c022-af022d33d826
Time: 2022-03-13 18:11:11Z

Power BI Desktop Error Details

Feedback Type:
Frown (Error)

Error Message:
The request was aborted: The request was canceled.

Stack Trace:
Microsoft.Mashup.Host.Document.SerializedException

Server stack trace:

Exception rethrown at [0]:
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.Mashup.OleDbProvider.DataHost.NonBufferingMashupEvaluator.MashupEvaluation.OnEvaluationComplete(AsyncResult`1 result)

Exception rethrown at [1]:
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.Mashup.OleDbProvider.MashupCommandEvaluation.get_PageReader()
at Microsoft.Mashup.OleDbProvider.MashupCommandEvaluation.Microsoft.OleDb.IEvaluationResultSource.WaitForResults()
at Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IEvaluationResultSource.WaitForResults()

Stack Trace Message:
The request was aborted: The request was canceled.

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.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass2_1.<SendAndMarshalExceptions>b__0(Object null)
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.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialog(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IQueryServices queryServices, IDesktopModelingHost modelingHost, LocalizedString title, LoadToModelContext loadToModelContext)
at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass16_0.<TryShowDialogForQueries>b__0()
at Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass64_0`1.<SynchronizeLooselyWithQueries>b__0()
at Microsoft.PowerBI.Client.Windows.Report.<SynchronizeLooselyWithQueriesAsync>d__65`1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueriesAsync[T](Func`1 getTask)
at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueries[T](Func`1 action)
at Microsoft.PowerBI.Client.Windows.Services.QueriesEditorWindowManager.ApplyQueryChanges(IEnumerable`1 queriesToLoad)
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__2()
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.102.845.0","ModuleName":"","Component":"Microsoft.Mashup.OleDbProvider.DataHost.NonBufferingMashupEvaluator.MashupEvaluation","Error":"Microsoft.Mashup.Host.Document.SerializedException - Microsoft.Mashup.Evaluator.Interface.ErrorException","MethodDef":"OnEvaluationComplete","ErrorOffset":""}

Snapshot Trace Logs:
C:\Users\Yetzx\Microsoft\Power BI Desktop Store App\FrownSnapShot09bf4f4d-aa48-40a0-b1a4-de5c25d457a3.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

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

Enabled Preview Features:
PBI_enableWebView2
PBI_mobileAuthoringFormattingUI
PBI_useModernFormatPane
PBI_sparklines

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

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBINonFatalError_ErrorDescription:
Microsoft.Mashup.Evaluator.Interface.ErrorException

PowerBIUserFeedbackServices_IsReported:
True

Formulas:

section Section1;

[ Description = "Neighborhood" ]shared #"SPD Public Crime Reports" = let
Source = OData.Feed("https://data.seattle.gov/api/odata/v4/tazs-3rd5", null, [Implementation="2.0"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"mcpp", "MCPP"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "offense_start_datetime", "offense_start_datetime - Copy"),
#"Inserted Parsed Date" = Table.AddColumn(#"Duplicated Column", "Parse", each Date.From(DateTimeZone.From([#"offense_start_datetime - Copy"])), type date),
#"Inserted Date" = Table.AddColumn(#"Inserted Parsed Date", "Date", each DateTime.Date([Parse]), type date),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Date",{"offense_start_datetime - Copy", "Parse"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"__id", "report_number", "offense_id", "offense_start_datetime", "Date", "offense_end_datetime", "report_datetime", "group_a_b", "crime_against_category", "offense_parent_group", "offense", "offense_code", "precinct", "sector", "beat", "MCPP", "_100_block_address", "longitude", "latitude"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Date", "Offense Start Date"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns1",{{"report_datetime", Order.Ascending}}),
#"Inserted Parsed Date1" = Table.AddColumn(#"Sorted Rows", "Parse", each Date.From(DateTimeZone.From([report_datetime])), type date),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Parsed Date1",{{"Parse", "Report_Date"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"__id", "report_number", "offense_id", "offense_start_datetime", "Offense Start Date", "offense_end_datetime", "report_datetime", "Report_Date", "group_a_b", "crime_against_category", "offense_parent_group", "offense", "offense_code", "precinct", "sector", "beat", "MCPP", "_100_block_address", "longitude", "latitude"})
in
#"Reordered Columns1";

[lbendlin]

Not sure if you need both the timestamp and the date but you can do a double dipping with the standard converter.

let
    Source = OData.Feed("https://data.seattle.gov/api/odata/v4/tazs-3rd5", null, [Implementation="2.0"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"report_datetime", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"report_datetime", type date}})
in
    #"Changed Type1"

[SeattleCrime]

Hello @lbendlin , thanks so much for replying to my query. 

I'll be honest that I'm quite a novice with Power BI and I honestly don't know what your suggestion means.  The Power BI Desktop seems like it's simply timing out around the 500MB mark after applying the data model update (parsing report_datetime).  I guess I could just forget about report_datetime and re-publish the rest of the file minus one of the visuals, because the OData error seemed to be disagreeing with offense_start_datetime. 

Are you suggesting a different way to parse report_datetime other than using the Add Column > Parse in the Power Query editor that would not cause the Power BI Desktop to hang?

[lbendlin · Accepted solution]

Maybe you can describe which reports you want to run off the data.  A lot of your transforms do not serve a real purpose (sorting rows or rearranging columns for example).  If you cut out all the unnecessary transforms you get the best performance.

[SeattleCrime]

Thanks @lbendlin , I didn't realize that the sort and column rearranging would bog down Power BI so much when applying changes.  I avoided taking those actions and was able to add the column and apply changes without issue.

I'm still unclear why exactly the report refresh ran into the data type error (below), but I'm going to publish this version that's been fully updated with the lastest data and see if hte public report will accept an auto-refresh.

The data provider was unable to convert the value '<pii></pii>' from the source data type 'VT_BSTR' to the expected data type 'VT_DATE'.

[lbendlin]

Could also have been caused by malformed data. I noticed a couple of empty date times and 0,0 locations.

Since it is an ODATA source you can consider prefiltering if that is appropriate.

The data loaded fine in my tests.

[SeattleCrime]

@lbendlinthanks again for your assistance.  The report I re-published was successfully auto-refreshed this morning with the automated batch via Power BI Service through OData's feed.