Forum Discussion
Pivot Chart from Power Query on New Excel Sheet throws exception 0x800A03EC
Hi PowerBIGuest,
I redo your steps, everything works fine without error. Please check your operation system is 32 bit while your excel is 64 bit? You can download my file(attchments) and check if it works fine on your computer.
Best Regards,
Angelia
I have an Excel 2016 workbook with a number of Power Queries off of a data table range. The output of several of those Power Queries are then used in Pivot Charts (each of which is located on its own sheet). This has been working fine for a month, but now I get an exception whenever I select one of the chart sheets.
I've created a minimum working example workbook from scratch (no relation to the workbook I've been using above, and with manually entered data) and I experience the same exception.
To create the minimum working example I typed the following data into the first sheet (which I called "raw-data"):
| col1 | col2 | col3 | |
| row1 | 5 | 5 | 5 |
| row2 | 5 | 2 | 5 |
| row3 | 3 | 3 | 4 |
| row4 | 4 | 5 | 5 |
| row5 | 4 | 4 | 3 |
| row6 | 5 | 5 | 2 |
| row7 | 5 | 3 | 5 |
| row8 | 2 | 4 | 5 |
I labelled the range 'raw-data'!$A$1:$D$9 with the name "data", and then I selected Data > Get & Transform Data > From Table/Range and create a Power Query called "data-query-out" as follows:
*************************************
let
Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"row1", Int64.Type}, {"row2", Int64.Type}, {"row3", Int64.Type}, {"row4", Int64.Type}, {"row5", Int64.Type}, {"row6", Int64.Type}, {"row7", Int64.Type}, {"row8", Int64.Type}})
in
#"Changed Type1"
*************************************
I named the worksheet that this Power Query's output is placed on "transformed-data". I then selected Insert > Pivot Chart and used "data-query-out" as the Table/Range input and chose for the Pivot Chart to be placed on a new worksheet. I named the Pivot Table sheet that is produced "pivot-data" and selected the checkboxes alongside each of the Pivot Chart Fields.
I then right clicked on the Pivot Chart and selected Move Chart, and selected New Chart, giving it the name "pivot-chart". I immediately get the following exception:
*************************************
Feedback Type:
Frown (Error)
Error Message:
Exception from HRESULT: 0x800A03EC
Stack Trace:
Microsoft.Mashup.Client.Excel.Shim.NativeExcelException: Exception from HRESULT: 0x800A03EC ---> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC
--- End of inner exception stack trace ---
at Microsoft.Mashup.Client.Excel.NativeExcelFunctionsBase.ValidateResult(Int32 result, Int32[] expectedValues)
at Microsoft.Mashup.Client.Excel.NativeExcelFunctionsC2R.Microsoft.Mashup.Client.Excel.INativeExcelFunctions.TryGetActiveListObject(IntPtr uiFrame, String& listObjectName)
at Microsoft.Mashup.Client.Excel.ActiveQueryCache.ActiveListObjectDescriptor.GetActive(IWindowContext windowContext, IWorkbookServices workbookServices)
at Microsoft.Mashup.Client.Excel.ActiveQueryCache.GetOrCreateCacheEntry(IWindowContext windowContext)
at Microsoft.Mashup.Client.Excel.CustomTaskPaneManagers.UpdateQueriesTaskPaneVisibility(IWindowContext windowContext)
at Microsoft.Mashup.Client.Excel.ExcelEventHandler.PerformPeriodicUpdate(IEnumerable`1 queriesTaskPaneVisibilityUpdateWindows)
at Microsoft.Mashup.Client.Excel.NativeEventHandler.<OnIdle>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.ClientShared.FeedbackErrorInfo..ctor(String message, Exception exception)
at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWin32Window activeWindow, WindowsHost windowsHost, FeedbackPackageInfo feedbackPackageInfo, Exception e)
at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.Mashup.Client.Excel.ExcelCallbackManager.InvokeAndReturnHResult(Action action)
Supports Premium Content:
True
Formulas:
section Section1;
shared #"data-query-out" = let
Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"row1", Int64.Type}, {"row2", Int64.Type}, {"row3", Int64.Type}, {"row4", Int64.Type}, {"row5", Int64.Type}, {"row6", Int64.Type}, {"row7", Int64.Type}, {"row8", Int64.Type}})
in
#"Changed Type1";
*************************************
If I move the Pivot Chart back to the "pivot-data" sheet so that it isn't on a new worksheet then switching back and forth to that sheet doesn't throw an exception. Moving the chart back to its own sheet triggers the exception again.
Anyone got ideas as to what's going wrong, and how to fix the issue?