Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Team,
Am trying to upload an excel into my power Bi Model and every time I do it gives me the above error below. The file extension is .XLSX but even a CSV or a .xls is not working
The error appears to be "We couldn't convert to Duration"
We can't see what steps are failing in the picture you have posted. Presumably there is a column which powerbi is trying to convert to Duration type and that fails due to problems with the data.
Thanks for the response. Below is the detail error that is generated.
Feedback Type:
Frown (Error)
Error Message:
[DataFormat.Error] We couldn't convert to Duration.
Stack Trace:
at Microsoft.Mashup.Engine1.Runtime.DurationValue.TryParse(String value, TimeSpan& timeSpan)
at Microsoft.Mashup.Engine1.Runtime.Library._Value.FromTextFunctionValue.GetValueAndType(Value text, CultureInfo cultureInfo, TypeValue& typeValue)
at Microsoft.Mashup.Engine1.Runtime.Library.ValueAndType.FromTextFunctionValue.TypedInvoke(Value text, Value culture)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue2`3.Invoke(Value arg0, Value arg1)
at Microsoft.Mashup.Host.Document.Formulas.EngineTextTypeDetector.DetectType(String text)
at Microsoft.Mashup.Host.Document.Formulas.OriginalAndInferredTypeDetector.NewValue(TypeHandle originalType, String value)
at Microsoft.Mashup.Host.Document.Formulas.AutoTypeDetector.PopulateDetectors(ITableView tableView, List`1 detectors, Int32 maxRows)
at Microsoft.Mashup.Host.Document.Formulas.AutoTypeDetector.CreateTableHeaderAndTypeDetector(IEngineHost engineHost, IEngine engine, ITableView tableView, Boolean allowHeaders, Int32 maxRows)
at Microsoft.Mashup.Host.Document.Formulas.TaskInvokeFormula.GetOrCreateTableHeaderAndTypeDetector(PackageEditor packageEditor, TaskCreationContext taskCreationContext, TableResult tableResult)
at Microsoft.Mashup.Host.Document.Formulas.PromoteHeadersFormula.TryCreate(FormulaReference baseFormulaReference, TaskCreationContext taskCreationContext, Boolean& showBuilder)
at Microsoft.Mashup.Host.Document.TaskFormula.TryCreateTaskFormula(FormulaSet formulaSet, IEngine engine, String formulaID, FormulaReference baseFormulaReference, TaskCreationContext selection, Formula& taskFormula, Boolean& showBuilder, Boolean& merged, Boolean& shouldRefreshParts)
at Microsoft.Mashup.Host.Document.FormulaEditor.ApplyTaskCore(PackageEditor packageEditor, FormulaSet formulaSet, String sectionName, String baseFormulaName, String baseFormulaPartName, String formulaID, TaskCreationContext taskCreationContext, String& actualFormulaName, String& actualFormulaPartName, TaskFormula& taskFormula, Boolean& hasBuilder, Boolean& merged, Boolean& shouldRefreshParts)
at Microsoft.Mashup.Host.Document.FormulaEditor.ApplyTask(PackageEditor packageEditor, FormulaSet formulaSet, String sectionName, String baseFormulaName, String baseFormulaPartName, String formulaID, TaskCreationContext taskCreationContext, ApplyTaskResult& result)
at Microsoft.Mashup.Host.Document.FormulaEditor.TryApplySubsequentFormulas(PackageEditor packageEditor, FormulaPath baseFormulaPath, PreviewResult previewResult, Boolean isHeaderDetectionEnabled, Boolean isTypeDetectionEnabled, ApplyTaskResult& applyTaskResult)
at Microsoft.Mashup.Host.Document.Model.Query.TryApplySubsequentFormulas(PreviewResult previewResult)
at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueryNavigatorPreviewHost.<>c__DisplayClass17_0.<SetPreviewFromQuery>b__1(PreviewReference preview)
at Microsoft.Mashup.Host.Document.Evaluation.PreviewEvaluationQueue.<>c__DisplayClass48_0.<OnPreviewComplete>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
Stack Trace Message:
[DataFormat.Error] We couldn't convert to Duration.
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.WebDialogs.WebDialog.<>n__0(IWindowHandle owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.Mashup.Client.UI.Shared.Ux.FloatingDialog.NavigatorFloatingDialog.ShowEditLoad(IWindowHandle owner, IUIHost uiHost, Query query, ITelemetryService telemetryService, Boolean multiSelectionEnabledByDefault, IWebTableImporterHost webTableImporterHost)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.ShowNavigator(Query query)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.ShowNavigatorOnSourceQuery(Query query, IEnumerable`1& importedQueries)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.ImportNavigationSource(Query query)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.GetPreviewResult(Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor, Nullable`1 explicitImportDestination)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.<>c__DisplayClass76_1.<OnQuerySettingsResolved>b__0()
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.WebDialogs.WebDialog.<>n__0(IWindowHandle owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_0.<Main>b__1()
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)
InnerException.Stack Trace Message:
Value 'Duration' was either too large or too small for TimeSpan.
InnerException.Stack Trace:
at System.Xml.XmlConvert.ToTimeSpan(String s)
at Microsoft.Mashup.Engine1.Runtime.DurationValue.TryParse(String value, TimeSpan& timeSpan)
InnerException.Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.GetFeedbackValuesFromException(Exception e, String prefix)
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.CreateAdditionalErrorInfo(Exception e)
at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
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.WebDialogs.WebDialog.<>n__0(IWindowHandle owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.Mashup.Client.UI.Shared.Ux.FloatingDialog.NavigatorFloatingDialog.ShowEditLoad(IWindowHandle owner, IUIHost uiHost, Query query, ITelemetryService telemetryService, Boolean multiSelectionEnabledByDefault, IWebTableImporterHost webTableImporterHost)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.ShowNavigator(Query query)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.ShowNavigatorOnSourceQuery(Query query, IEnumerable`1& importedQueries)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.ImportNavigationSource(Query query)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.GetPreviewResult(Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor, Nullable`1 explicitImportDestination)
at Microsoft.Mashup.Client.UI.Shared.DataImporter.<>c__DisplayClass76_1.<OnQuerySettingsResolved>b__0()
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.WebDialogs.WebDialog.<>n__0(IWindowHandle owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass4_0.<Main>b__1()
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)
Model Default Mode:
Empty
Snapshot Trace Logs:
C:\Users\sgitonga\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot507270321.zip
Performance Trace Logs:
C:\Users\sgitonga\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_NewWebTableInference
PBI_showIncrementalRefreshPolicy
PBI_showManageAggregations
PBI_improvedFilterExperience
PBI_qnaLiveConnect
PBI_keyDrivers
PBI_userFavoriteResourcePackagesEnabled
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
Formulas:
section Section1;
shared Sheet = let
Source = Excel.Workbook(File.Contents("C:\Users\sgitonga\OneDrive - Simba Corporation Ltd\Desktop\NV BI POC\2017 NV Sales.xls"), null, true)
in
Source;
shared #"Sheet (2)" = let
Source = Excel.Workbook(File.Contents("C:\Users\sgitonga\OneDrive - Simba Corporation Ltd\Desktop\NV BI POC\2017 NV Sales.xls"), null, true),
Sheet1 = Source{[Name="Sheet"]}[Data]
in
Sheet1;
shared #"NavigatorBase_b173928b-485f-44a2-b9fb-d92334187bb9" = let
Source = Excel.Workbook(File.Contents("C:\Users\sgitonga\OneDrive - Simba Corporation Ltd\Desktop\NV BI POC\2017 NV Sales.xls"), null, true)
in
Source;