Error Message: Attempted to read or write protected memory.

• Question

• 

  

  0

  Sign in to vote

  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