none
Error Message: Attempted to read or write protected memory. RRS feed

  • Question

  • I am using Power Query in Excel to manage data in the file. We're not using any other applications. 
    The files are generating the error message: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

    The full error message is similar to that which other users have reported recently on this forum. I have pasted it at the bottom of the message. 

    It's become an issue because we've built the workbook for a critical business process and some users are experiencing crash up to 10x a day. 

    Can anyone advise on: 
    1) Whether Microsoft can provide a software patch for Windows/Excel/Power Query that resolves the issue? Or, 
    2) How we can prevent the error occuring in the Power Query in the first place? 

    ***Further details:***

    - Only Excel application is being used. Files are stand-alone and don't draw data from any other workbooks. Power Query is being used to link tables and create large pivot tables. 

    - Error occurs most when refreshing pivot tables, but sometimes occurs randomly (e.g. when users are updating input data, etc). 

    - Error is affecting files being used on all six different machines in two different offices who are using the files. 

    - There don’t appear to be version issues that would explain the error. All machines are on Windows 10 Pro; all on 64-bit Windows and Excel. All have been upgraded to Excel Excel for Office 365 MSO 64-bit; all but one machine has 8GB RAM. All have i5 or i7 processors. 

    - Users with larger versions of the files experience the issue more frequently. One user has 4GB RAM rather than 8GB - they experience the issue more often. This lends itself to the error being a memory issue. 

    I am happy to share an example file with Microsoft. 


    ***FULL ERROR MESSAGE***

    Feedback Type:
    Frown (Error)

    Error Message:
    Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

    Stack Trace:

    Server stack trace: 
       at Microsoft.Mashup.Client.Excel.NativeExcelFunctionsC2R.GetNamedObjects(IntPtr workbookPointer)
       at Microsoft.Mashup.Client.Excel.ExcelHost.<>c__DisplayClass4.<TryGetTableNames>b__3()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass6`1.<SendAndMarshalExceptions>b__5()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3.<SendAndMarshalExceptions>b__0(Object null)

    Exception rethrown at [0]: 
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions[T](SynchronizationContext context, Func`1 callback)
       at Microsoft.Mashup.Client.Excel.ExcelHost.TryGetTableNames(PackageReference packageReference, String[]& tableNames)
       at Microsoft.Mashup.Host.Document.Evaluation.EvaluatorHost.<CreateSession>b__8(PackageReference packageRef)
       at Microsoft.Mashup.Host.Document.Evaluation.ExcelService.TryGetTableNames(String[]& tableNames)
       at Microsoft.Mashup.Host.Document.Evaluation.Services.RemoteExcelServiceFactory.Stub.OnTableNamesRequest(IMessageChannel channel, TableNamesRequestMessage request)
       at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(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.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
       at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(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_TableSource()
       at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_TableSource()
       at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)
       at Microsoft.Mashup.Host.Document.Analysis.CachingDocumentAnalysisInfo.CachingPartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)
       at Microsoft.Mashup.DocumentHost.DocumentAnalyzer.Analyze(IPartitionAnalysisInfo partitionInfo, AccessRecorder recorder, EvaluationResult2`1 result, Action`1 callback)
       at Microsoft.Mashup.DocumentHost.DocumentAnalyzer.<>c__DisplayClass10.<BeginAnalyzeDocumentPartition>b__d(EvaluationResult2`1 result)
       at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose(Action`1 callback, EvaluationResult2`1 result, Action action)
       at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose[T](Action`1 callback, EvaluationResult2`1 result, Action action)
       at Microsoft.Mashup.Evaluator.LimitedDocumentEvaluatorFactory.Evaluation`1.Complete(EvaluationResult2`1 result)
       at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose(Action`1 callback, EvaluationResult2`1 result, Action action)
       at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose[T](Action`1 callback, EvaluationResult2`1 result, Action action)
       at Microsoft.Mashup.Evaluator.LimitedDocumentEvaluatorFactory.Evaluation`1.Complete(EvaluationResult2`1 result)
       at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose(Action`1 callback, EvaluationResult2`1 result, Action action)
       at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose[T](Action`1 callback, EvaluationResult2`1 result, Action action)
       at Microsoft.Mashup.Evaluator.DocumentEvaluator.<>c__DisplayClass3`1.<BeginGetResult>b__0(EvaluationResult2`1 result)
       at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose(Action`1 callback, EvaluationResult2`1 result, Action action)
       at Microsoft.Mashup.Evaluator.Interface.EvaluationResultExtensions.InvokeThenOnDispose[T](Action`1 callback, EvaluationResult2`1 result, Action action)
       at Microsoft.Mashup.Evaluator.LimitedDocumentEvaluatorFactory.Evaluation`1.Complete(EvaluationResult2`1 result)
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation`1.TryComplete(EvaluationResult2`1 result)
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.PreviewValueSourceRemoteEvaluation.GetResult(Boolean enableFirewall)
       at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.RemoteEvaluation.Evaluate(Boolean enableFirewall)
       at Microsoft.Mashup.Evaluator.EvaluatorThreadPool.EvaluatorThread(Object state)
       at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)

    Stack Trace Message:
    Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

    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, String messageDetail)
       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.<>c__DisplayClass3.<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.ThreadContext.LocalModalMessageLoop(Form form)
       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__FabricatedMethod8(IWindowHandle )
       at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
       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.<>c__DisplayClass3.<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.ThreadContext.LocalModalMessageLoop(Form form)
       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__FabricatedMethod8(IWindowHandle )
       at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
       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.<>c__DisplayClass3.<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.ThreadContext.LocalModalMessageLoop(Form form)
       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__FabricatedMethod8(IWindowHandle )
       at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
       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.<>c__DisplayClass3.<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.ThreadContext.LocalModalMessageLoop(Form form)
       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__FabricatedMethod8(IWindowHandle )
       at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
       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.<>c__DisplayClass3.<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)


    Supports Premium Content:
    True

    Formulas:


    section Section1;

    shared Output = let
        Source = Excel.CurrentWorkbook(),
        #"Filter on Range Rundown" = Table.SelectRows(Source, each ([Name] = "Rundown")),
        #"Get Rundown columns" = Table.ExpandTableColumn(#"Filter on Range Rundown", "Content", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51"}, {"Content.Column1", "Content.Column2", "Content.Column3", "Content.Column4", "Content.Column5", "Content.Column6", "Content.Column7", "Content.Column8", "Content.Column9", "Content.Column10", "Content.Column11", "Content.Column12", "Content.Column13", "Content.Column14", "Content.Column15", "Content.Column16", "Content.Column17", "Content.Column18", "Content.Column19", "Content.Column20", "Content.Column21", "Content.Column22", "Content.Column23", "Content.Column24", "Content.Column25", "Content.Column26", "Content.Column27", "Content.Column28", "Content.Column29", "Content.Column30", "Content.Column31", "Content.Column32", "Content.Column33", "Content.Column34", "Content.Column35", "Content.Column36", "Content.Column37", "Content.Column38", "Content.Column39", "Content.Column40", "Content.Column41", "Content.Column42", "Content.Column43", "Content.Column44", "Content.Column45", "Content.Column46", "Content.Column47", "Content.Column48", "Content.Column49", "Content.Column50", "Content.Column51"}),
        #"Get Rundown data" = Table.SelectRows(#"Get Rundown columns", each true),
        #"Rename Columns" = Table.PromoteHeaders(#"Get Rundown data", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Rename Columns",{{"Product_ID", type text}, {"Account_ID", type text}, {"SSRD", type text}, {"Desc", type any}, {"LY", type any}, {"CY", Int64.Type}, {"YTD", type any}, {"YTG", type any}, {"H_24", type any}, {"H_23", type any}, {"H_22", type any}, {"H_21", type any}, {"H_20", type any}, {"H_19", type any}, {"H_18", type any}, {"H_17", type any}, {"H_16", type any}, {"H_15", type any}, {"H_14", type any}, {"H_13", type any}, {"H_12", type any}, {"H_11", type any}, {"H_10", type any}, {"H_9", type any}, {"H_8", type any}, {"H_7", type any}, {"H_6", type any}, {"H_5", type any}, {"H_4", type any}, {"H_3", type any}, {"H_2", type any}, {"H_1", type any}, {"E_0", type any}, {"F_1", type any}, {"F_2", type any}, {"F_3", type any}, {"F_4", type any}, {"F_5", type any}, {"F_6", type any}, {"F_7", type any}, {"F_8", type any}, {"F_9", type any}, {"F_10", type any}, {"F_11", type any}, {"F_12", type any}, {"F_13", type any}, {"F_14", type any}, {"F_15", type any}, {"F_16", type any}, {"F_17", type any}, {"F_18", type any}, {"Rundown", type text}}),
        #"Remove Titles from data" = Table.SelectRows(#"Changed Type", each ([Account_ID] <> null and [Account_ID] <> "Sch" and [Account_ID] <> "Title" and [Account_ID] <> "Veh")),
        #"Transpose on period" = Table.UnpivotOtherColumns(#"Remove Titles from data", {"Product_ID", "Account_ID", "SSRD", "Desc", "Rundown"}, "Attribute", "Value"),
        #"Get transpose" = Table.SelectRows(#"Transpose on period", each true),
        #"Clean Columns" = Table.RemoveColumns(#"Get transpose",{"Rundown", "SSRD"}),
        #"Get Monthly information" = Table.NestedJoin(#"Clean Columns",{"Attribute"},Months,{"Time_ID"},"Months",JoinKind.LeftOuter),
        #"Expanded Months" = Table.ExpandTableColumn(#"Get Monthly information", "Months", {"Value"}, {"Period"}),
        #"Remove Zeros to surpress data" = Table.SelectRows(#"Expanded Months", each ([Value] <> 0)),
        #"Changed Type1" = Table.TransformColumnTypes(#"Remove Zeros to surpress data",{{"Value", type number}})
    in
        #"Changed Type1";

    shared Months = let
        Source = Excel.CurrentWorkbook(),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Time")),
        #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48"}, {"Content.Column1", "Content.Column2", "Content.Column3", "Content.Column4", "Content.Column5", "Content.Column6", "Content.Column7", "Content.Column8", "Content.Column9", "Content.Column10", "Content.Column11", "Content.Column12", "Content.Column13", "Content.Column14", "Content.Column15", "Content.Column16", "Content.Column17", "Content.Column18", "Content.Column19", "Content.Column20", "Content.Column21", "Content.Column22", "Content.Column23", "Content.Column24", "Content.Column25", "Content.Column26", "Content.Column27", "Content.Column28", "Content.Column29", "Content.Column30", "Content.Column31", "Content.Column32", "Content.Column33", "Content.Column34", "Content.Column35", "Content.Column36", "Content.Column37", "Content.Column38", "Content.Column39", "Content.Column40", "Content.Column41", "Content.Column42", "Content.Column43", "Content.Column44", "Content.Column45", "Content.Column46", "Content.Column47", "Content.Column48"}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Content", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time_ID", type text}, {"LY", type any}, {"CY", type any}, {"YTD", type any}, {"YTG", type any}, {"H_24", type any}, {"H_23", type any}, {"H_22", type any}, {"H_21", type any}, {"H_20", type any}, {"H_19", type any}, {"H_18", type any}, {"H_17", type any}, {"H_16", type any}, {"H_15", type any}, {"H_14", type any}, {"H_13", type any}, {"H_12", type any}, {"H_11", type any}, {"H_10", type any}, {"H_9", type any}, {"H_8", type any}, {"H_7", type any}, {"H_6", type any}, {"H_5", type any}, {"H_4", type any}, {"H_3", type any}, {"H_2", type any}, {"H_1", type any}, {"E_0", type any}, {"F_1", type any}, {"F_2", type any}, {"F_3", type any}, {"F_4", type any}, {"F_5", type any}, {"F_6", type any}, {"F_7", type any}, {"F_8", type any}, {"F_9", type any}, {"F_10", type any}, {"F_11", type any}, {"F_12", type any}, {"F_13", type any}, {"F_14", type any}, {"F_15", type any}, {"F_16", type any}, {"F_17", type any}, {"F_18", type any}, {"Time", type text}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Time_ID"}, "Attribute", "Value"),
        #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Columns", each ([Time_ID] = "Desc")),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Time_ID"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Time_ID"}})
    in
        #"Renamed Columns";

    shared Output_OEM = let
        Source = Output,
        #"Filtered Rows" = Table.SelectRows(Source, each ([Account_ID] = "LS" or [Account_ID] = "NOT" or [Account_ID] = "OB" or [Account_ID] = "PRD" or [Account_ID] = "PRD_GD" or [Account_ID] = "SLS")),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.EndsWith([Period], Year_YY)),
        #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Attribute] <> "CY" and [Attribute] <> "LY" and [Attribute] <> "YTD" and [Attribute] <> "YTG") and ([Value] <> 0)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows2",{"Account_ID"}),
        #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each [Period]),
        #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Custom", Order.Ascending}}),
        #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Custom"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Desc", "Product_ID", "Attribute", "Value", "Period"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Attribute"}),
        #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Period]), "Period", "Value", List.Sum)
    in
        #"Pivoted Column";

    [ Description = "Please enter a year for the output to the OEM in the format YY" ]
    shared Year_YY = "19" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true];

    shared Product = let
        Source = Excel.CurrentWorkbook(){[Name="Product"]}[Content]
    in
        Source;

    shared Product_Output = let
        Source = Table.NestedJoin(Output,{"Product_ID"},Product,{"Product Code"},"Product",JoinKind.FullOuter),
        #"Expanded Product1" = Table.ExpandTableColumn(Source, "Product", {"Brand", "Model", "Supply restriction indicator"}, {"Brand", "Model", "Supply restriction indicator"})
    in
        #"Expanded Product1";

    shared Product_Output_Brand = let
        Source = Product_Output,
        #"Grouped Rows" = Table.Group(Source, {"Brand", "Account_ID", "Desc", "Period"}, {{"Value", each List.Sum([Value]), type number}}),
        #"Renamed Columns1" = Table.RenameColumns(#"Grouped Rows",{{"Brand", "Product_ID"}}),
        #"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([Product_ID] <> null))
    in
        #"Filtered Rows";

    shared Product_Output_Model = let
        Source = Product_Output,
        #"Grouped Rows" = Table.Group(Source, {"Model", "Account_ID", "Desc", "Period"}, {{"Value", each List.Sum([Value]), type number}}),
        #"Renamed Columns1" = Table.RenameColumns(#"Grouped Rows",{{"Model", "Product_ID"}}),
        #"Filtered Rows" = Table.SelectRows(#"Renamed Columns1", each ([Product_ID] <> null))
    in
        #"Filtered Rows";

    shared Product_Output_Report = let
        Source = Product_Output,
        #"Grouped Rows" = Table.Group(Source, {"Supply restriction indicator", "Account_ID", "Desc", "Period"}, {{"Value", each List.Sum([Value]), type number}}),
        #"Renamed Columns1" = Table.RenameColumns(#"Grouped Rows",{{"Supply restriction indicator", "Product_ID"}}),
        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns1", each ([Product_ID] <> null)),
        #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Product_ID] <> null))
    in
        #"Filtered Rows";

    shared #"Report Out" = let
        Source = Table.Combine({Product_Output_Brand, Product_Output_Model, Product_Output_Report})
    in
        Source;

    shared #"Report Out (2)" = let
        Source = Table.Combine({Product_Output_Brand, Product_Output_Model, Product_Output_Report}),
        #"Appended Query" = Table.Combine({Source, Targets_Model, Targets_Brand, Output}),
        #"Removed Columns" = Table.RemoveColumns(#"Appended Query",{"Attribute"})
    in
        #"Removed Columns";

    shared Targets_Model = let
        Source = Excel.CurrentWorkbook(){[Name="Targets"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Model", type text}, {"Target Type", type text}, {"Account_ID", type text}, {"LY", Int64.Type}, {"CY", Int64.Type}, {"YTD", Int64.Type}, {"YTG", Int64.Type}, {"H_24", Int64.Type}, {"H_23", Int64.Type}, {"H_22", Int64.Type}, {"H_21", Int64.Type}, {"H_20", Int64.Type}, {"H_19", Int64.Type}, {"H_18", Int64.Type}, {"H_17", Int64.Type}, {"H_16", Int64.Type}, {"H_15", Int64.Type}, {"H_14", Int64.Type}, {"H_13", Int64.Type}, {"H_12", Int64.Type}, {"H_11", Int64.Type}, {"H_10", Int64.Type}, {"H_9", Int64.Type}, {"H_8", Int64.Type}, {"H_7", Int64.Type}, {"H_6", Int64.Type}, {"H_5", Int64.Type}, {"H_4", Int64.Type}, {"H_3", Int64.Type}, {"H_2", Int64.Type}, {"H_1", Int64.Type}, {"E_0", Int64.Type}, {"F_1", Int64.Type}, {"F_2", Int64.Type}, {"F_3", Int64.Type}, {"F_4", Int64.Type}, {"F_5", Int64.Type}, {"F_6", Int64.Type}, {"F_7", Int64.Type}, {"F_8", Int64.Type}, {"F_9", Int64.Type}, {"F_10", Int64.Type}, {"F_11", Int64.Type}, {"F_12", Int64.Type}, {"F_13", Int64.Type}, {"F_14", Int64.Type}, {"F_15", Int64.Type}, {"F_16", Int64.Type}, {"F_17", Int64.Type}, {"F_18", Int64.Type}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Model", "Target Type", "Account_ID"}, "Attribute", "Value"),
        #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns",{"Attribute"},Months,{"Time_ID"},"Months",JoinKind.LeftOuter),
        #"Expanded Months" = Table.ExpandTableColumn(#"Merged Queries", "Months", {"Value"}, {"Months.Value"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Months",{"Attribute"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Model", "Target Type", "Account_ID", "Months.Value", "Value"}),
        #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Months.Value", "Period"}}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns",{"Model", "Account_ID", "Target Type", "Period", "Value"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Target Type", "Desc"}, {"Model", "Product_ID"}})
    in
        #"Renamed Columns1";

    shared Targets_Brand = let
        Source = Table.NestedJoin(Targets_Model,{"Product_ID"},Product,{"Model"},"Product",JoinKind.LeftOuter),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.First([Product])),
        #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Brand"}, {"Custom.Brand"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Product"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom.Brand", "Product.Brand"}}),
        #"Grouped Rows" = Table.Group(#"Renamed Columns1", {"Product.Brand", "Account_ID", "Desc", "Period"}, {{"Value", each List.Sum([Value]), type number}}),
        #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Product.Brand", "Product_ID"}})
    in
        #"Renamed Columns";


    Wednesday, January 30, 2019 2:30 AM

Answers

  • Hi,

    Thank for sharing.
    We submitted a fix to this issue, and it should be released during February (in build 11328).

    Please let us know if the issue persists after the update.

    Thanks,
    Tal - Excel team.

    Thursday, January 31, 2019 1:22 PM

All replies

  • Hi,

    Thank for sharing.
    We submitted a fix to this issue, and it should be released during February (in build 11328).

    Please let us know if the issue persists after the update.

    Thanks,
    Tal - Excel team.

    Thursday, January 31, 2019 1:22 PM
  • Thanks so much! 

    Will keep you posted. 

    Friday, February 1, 2019 2:44 AM
  • Dear Tal, 

    Can you confirm if this proposed fix was included in build 11328? 

    My team are still having the same issues, and I wanted to check on the schedule for the fix. 

    Many thanks,

    Alex

    Thursday, February 28, 2019 1:04 AM
  • Hi Alex,

    Can you please share your Excel build number?

    Thanks.

    Monday, March 4, 2019 2:53 PM
  • Hi Tal, 

    I've got version 1902, build 11328.20146. 

    I noticed on the Microsoft website that a new build was released recently, but only to the early-adopter community. It would be very helpful to know when the fix will be released to the general user-base. 

    Many thanks! 

    Alex

    Tuesday, March 12, 2019 2:55 AM
  • Hi Alex,

    Your Excel version should already contain the fix for this issue. Are you still seeing this issue on machines that have this update?

    You mentioned that you might be able to share an example file. I'd really appreciate if you can share it with me, please make sure to remove any sensitive data or scramble it before sharing it.
    If you can, please also include the full error message (as included in the original post), and the steps you take to reproduce the error. If you prefer not to share it here, you can send it to tapeleg@microsoft.com.

    Thanks,
    Tal - Excel team.

    Tuesday, March 12, 2019 1:55 PM
  • Hi Tal, 

    Thanks for you support. The issue seems to have resolved itself since the end of Feb - I checked with my teams and they've not had a recurrence. Will keep you posted if it recurs, but seems like it's fixed. Thanks! 

    Alex

    Friday, March 15, 2019 3:00 AM