none
Power Query will not load ODBC data RRS feed

  • Question

  • I have ODBC connections to an iSeries. I have been using these connections without issue for years. All the sudden, I get an "Unexpected Error" and the data will not load. The full error is below: Already tried to uninstall and reinstall Office 2016.

    Feedback Type:
    Frown (Error)

    Error Message:
    Object reference not set to an instance of an object.

    Stack Trace:
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.<>c__DisplayClass4.<InvokeScript>b__3()
       at Microsoft.Mashup.Client.UI.Windows.JsErrorHandler.WrapInvokeScript(Func`1 invokeScript)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.InvokeScript(String script, Boolean bypassDebugHost)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke(String script)
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.DeleteSubTree(TreeNode parent, TreeNode[] subTree)
       at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.OnDeletingSubTree(TreeNode parent, TreeNode[] subTree)
       at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.InternalDeletingSubTree(TreeNode parent, TreeNode[] subTree)
       at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.InternalDeletingSubTree(TreeNode parent, TreeNode[] subTree)
       at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.InternalDeletingSubTree(TreeNode parent, TreeNode[] subTree)
       at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.InternalDeletingSubTree(TreeNode parent, TreeNode[] subTree)
       at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.OnTreeNodeSubTreeChanged(Object sender, CollectionChangeEventArgs e)
       at Microsoft.Mashup.Host.Models.Tree.TreeNode.OnSubTreeRemoved(TreeNode[] value)
       at Microsoft.Mashup.Host.Models.Tree.TreeNode.set_SubTree(TreeNode[] value)
       at Microsoft.Mashup.Host.Models.Tree.TreeNode.ClearSubTree()
       at Microsoft.Mashup.Host.Models.Tree.NavigatorModel.Dispose()
       at Microsoft.Mashup.Client.UI.Shared.Ux.FloatingDialog.NavigatorFloatingDialog.Dispose(Boolean disposing)
       at Microsoft.Mashup.Client.UI.Shared.TestUtilities.InfiniteLifetimeOleMarshalObject.Dispose()
       at Microsoft.Mashup.Client.UI.Shared.Ux.FloatingDialog.NavigatorFloatingDialog.ShowEditLoadTo(IWindowHandle owner, IUIHost uiHost, Query query, ITelemetryService telemetryService, ITeachingCalloutManager teachingCalloutManager)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClass1c.<ShowNavigator>b__1b(IWorkbook workbook)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClassa.<>c__DisplayClassc.<NotifyGetDataPresence>b__9()
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.NotifyGetDataPresence(IWorkbook workbook, Action action)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClassa.<NotifyGetDataPresence>b__8(IWorkbook workbook)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClassf.<InvokeOnWorkbook>b__e(IWorkbook workbook, IWindowContext windowContext)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.InvokeOnWorkbook[T](Func`3 action, T defaultValue)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.InvokeOnWorkbook(Action`1 action)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.NotifyGetDataPresence(Action`1 action)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.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.Excel.ExcelDataImporter.<>c__DisplayClass20.<GetPreviewResult>b__1f()
       at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClass20.<GetPreviewResult>b__1e(IWorkbook workbook)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClassa.<>c__DisplayClassc.<NotifyGetDataPresence>b__9()
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.NotifyGetDataPresence(IWorkbook workbook, Action action)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClassa.<NotifyGetDataPresence>b__8(IWorkbook workbook)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.<>c__DisplayClassf.<InvokeOnWorkbook>b__e(IWorkbook workbook, IWindowContext windowContext)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.InvokeOnWorkbook[T](Func`3 action, T defaultValue)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.InvokeOnWorkbook(Action`1 action)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.NotifyGetDataPresence(Action`1 action)
       at Microsoft.Mashup.Client.Excel.ExcelDataImporter.GetPreviewResult(Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor, Nullable`1 explicitImportDestination)
       at Microsoft.Mashup.Client.UI.Shared.DataImporter.<>c__DisplayClass20.<OnQuerySettingsResolved>b__1d()
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

    Stack Trace Message:
    Object reference not set to an instance of an object.

    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.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClassf.<Post>b__e(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.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


    Supports Premium Content:
    True

    Formulas:


    section Section1;

    shared #"NavigatorBase_e4a26a89-90e8-4557-9831-700d0e469e2f" = let
        Source = Odbc.DataSource("dsn=RACPP", [HierarchicalNavigation=true])
    in
        Source;

    shared #"NavigatorBase_875a6af1-fff0-4045-a6e8-a3408414f971" = let
        Source = Odbc.DataSource("dsn=RACPP", [HierarchicalNavigation=true])
    in
        Source;

    shared #"NavigatorBase_03f2aff6-5b88-4b32-9919-a0fbdf43f2f3" = let
        Source = Odbc.DataSource("dsn=RACPP", [HierarchicalNavigation=true])
    in
        Source;

    Tuesday, August 14, 2018 3:07 PM

Answers

  • Thanks. This problem magically went away. Likely due to some Windows updates. WU frequently wreaks havoc with my .NET version to which the iSeries seems particularly sensitive to. Anyway, so far so good. 
    Thursday, September 13, 2018 8:57 PM

All replies

  • Note: This data source works fine with other apps (such as MS Access). I did notice that it does not prompt me for a password in Power Query like other programs do. Could this be the issue? How do I get it to prompt me again?
    Tuesday, August 14, 2018 4:54 PM
  • I found where to see the stored credentials and I cleared them. This did not help. I created a new DSN using the iSeries Access ODBC driver (32-bit). This does not work either. This same driver works just fine on another machine. I'm at a loss and unable to load any data at this point.
    Tuesday, August 14, 2018 7:33 PM
  • Are you seeing same error if you were to use Power BI Desktop? Can you provide the ODBC driver version that you are using? Any change in the environment after which you started seeing the error? 

    Have you tried the DB2 connector shipped with PBI Desktop/Excel? Our team has recently shipped some improvements for DB2 connector. 

    Thursday, September 13, 2018 7:33 PM
  • Thanks. This problem magically went away. Likely due to some Windows updates. WU frequently wreaks havoc with my .NET version to which the iSeries seems particularly sensitive to. Anyway, so far so good. 
    Thursday, September 13, 2018 8:57 PM
  • I'd be interested in knowing if you have a use case to use the ODBC driver. Have you tried the DB2 connector in PBI Desktop?
    Thursday, September 13, 2018 9:33 PM
  • Not using PBI Desktop at this time, just Power Pivot. If we ever start building dashboards (been talking about it forever it seems) I'll make a note to look at the DB2 connector. Most likely, though, we will stage the data on SQL Server before we build PBI on top of it.

    Thanks.

    Friday, September 14, 2018 1:34 PM