Forum Discussion
Error Specified key is not a valid size for this algorithm
Hey Greg_Deckler , I am extracting from an SAP Database:
let
Source = SapHana.Database("mrkhana01:30015", [Query="SELECT * from ""EDW_STAGING"".""CORPORATE_SHAREPOINT_DAILY_PRODUCTION_NUMBERS"""]),
#"Removed Columns" = Table.RemoveColumns(Source,{"ORG_KEY", "MODIFIED_BY", "MODIFIED_DATE", "CREATED_BY", "CREATED_DATE", "BATCH_ID_ORIGINAL", "BATCH_ID_UPDATED", "ID"}),
#"Filtered Rows" = Table.RemoveColumns(Source,{"ORG_KEY", "MODIFIED_BY", "MODIFIED_DATE", "CREATED_BY", "CREATED_DATE", "BATCH_ID_ORIGINAL", "BATCH_ID_UPDATED", "ID"}),
#"Filtered Rows3" = Table.SelectRows(#"Filtered Rows", each ([ESTIMATE_FORECAST] = "Estimate")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows3",{{"DATE", "Date"}, {"MINE", "Mine"}, {"ESTIMATE_FORECAST", "Plan Version"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Date", "Mine", "Plan Version"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Metric"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Version", each "Actuals"),
in
#"Added Custom"
When I try to Pivot the data, the error is thrown. I would add the step in the Query Editor and it appears like this in the Advanced Editor:
#"Pivoted Column1" = Table.Pivot(#"Renamed Columns2", List.Distinct(#"Renamed Columns2"[#"Metric Code"]), "Metric Code", "Actuals", List.Sum),
We are also getting this same error across the board with Excel 2016. Attempting to connect to SQL Server. Just started today 4/24/2017. No issues in Power BI Desktop - just Excel 2016 Pro:
Feedback Type:
Frown (Error)
Timestamp:
2017-04-25T00:24:38.2078052Z
Local Time:
2017-04-24T18:24:38.2078052-06:00
Product Version:
2.44.4675.243 (PQ-CY17SU03) (x64)
Release:
March 2017
Excel Version:
16.0.4522.1000
Excel Install Location:
C:\Program Files\Microsoft Office\Office16\EXCEL.EXE
IE Version:
11.1066.14393.0
OS Version:
Microsoft Windows NT 10.0.14393.0 (x64 en-US)
CLR Version:
4.6.2. or later [Release Number = 394802]
Workbook Package Info:
1* - en-US, fastCombine: Disabled, runBackgroundAnalysis: True.
Peak Working Set:
325 MB
Private Memory:
286 MB
Peak Virtual Memory:
34 GB
Error Message:
The specified key is not a valid size for this algorithm.
Parameter name: key
Stack Trace:
Microsoft.Mashup.Evaluator.Interface.ErrorException: The specified key is not a valid size for this algorithm.
Parameter name: key ---> System.ArgumentException: The specified key is not a valid size for this algorithm.
Parameter name: key ---> System.ArgumentException: The specified key is not a valid size for this algorithm.
Parameter name: key
at System.Security.Cryptography.AesCryptoServiceProvider.CreateEncryptor(Byte[] key, Byte[] iv)
at Microsoft.Mashup.EngineHost.Services.EncryptedPersistentCache.EncryptedStorage.EncryptPage(Byte[] input, Int32 count, Byte[] output)
at Microsoft.Mashup.EngineHost.Services.TransformingPagedStream.WritePage(Int32 page, Byte[] buffer, Int32 length)
at Microsoft.Mashup.EngineHost.Services.PagedStream.Flush()
at Microsoft.Mashup.EngineHost.Services.EncryptedPersistentCache.EncryptedStorage.CommitStream(Int32 id, Stream stream)
at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(String key, TemporaryStorage storage)
at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(Object obj)
at Microsoft.Mashup.Evaluator.EvaluatorThreadPool.EvaluatorThread(Object state)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass7.<TryReportException>b__6()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryHandleException(Exception exception)
at Microsoft.Mashup.Evaluator.SafeThread2.HandleException(Exception e)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
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.Threading.ThreadHelper.ThreadStart(Object obj)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(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_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)
Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.ClientShared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace)
at Microsoft.Mashup.Client.ClientShared.DataImporter.HandleImportEvaluationException(ExceptionResult exceptionView, Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.ClientShared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
at Microsoft.Mashup.Client.ClientShared.DataImporter.<>c__DisplayClass1d.<OnQuerySettingsResolved>b__1a()
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)
Supports Premium Content:
True
Formulas:
section Section1;
shared Query1 = let
Source = Sql.Databases("db03snlnt\pr")
in
Source;
- BrianFleming9 years agoFrequent Visitor
Had any luck with this? Having the same issue.
- BrianFleming9 years agoFrequent Visitor
I've found a solution
Essentially, you just go to Source
Change Open As to automatic and click OK
#
Double-click the Excel Icon when it comes up
Insert Step
And it usually fixes it, not sure if this will work on other data sources but, it works for me in Excel
- BrianFleming9 years agoFrequent Visitor
Wouldn't bother with the above, needs resetting everytime the dashboard is opened