Error Specified key is not a valid size for this algorithm

[scottkad]

So I've been doing some testing using different data sources (Web, Excel File, SharePoint Folder...) and queries that were working previously are now broken. This is the error in Query Editor:

 Does anyone know what causes this to happen and how to fix it?

[Anonymous]

Unexpected error: The specified key is not a valid size for this algorithm.
Parameter name: key
Details:
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)

[princess2018]

what did you do to solve this

[Greg_Deckler]

Can you post all of you query code so that we can see this in context?

[scottkad]

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),

[caruybal]

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;

[BrianFleming]

Had any luck with this? Having the same issue.

[BrianFleming]

I've found a solution

https://powerpivotpro.com/2014/07/i-modified-an-existing-table-in-power-query-and-now-it-wont-refres...

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

[BrianFleming]

Wouldn't bother with the above, needs resetting everytime the dashboard is opened

[BrianFleming]

Clearing the cache did actually work properly - Adding for Google searchers

https://social.technet.microsoft.com/Forums/en-US/e5c8a3b6-2604-41c3-88cf-152b69b6c091/microsoftmash...

[scottkad]

Thanks for the suggestions @BrianFleming, I have tried clearing the cache - no luck for me yet. 

[BrianFleming]

What data sources are you trying top connect to?

What types of fields have you got in the data?

[Anonymous]

I have same problem.  and I have clear cache and also re-installed latest version of PBI.  Still doesn't work.

Unexpected error: The specified key is not a valid size for this algorithm.
Parameter name: key
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: The specified key is not a valid size for this algorithm.
Parameter name: key ---&gt; System.ArgumentException: The specified key is not a valid size for this algorithm.
Parameter name: key ---&gt; 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.&lt;&gt;c__DisplayClass15.&lt;CreateAction&gt;b__14(Object o)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.&lt;&gt;c__DisplayClass7.&lt;TryReportException&gt;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.&lt;&gt;c__DisplayClass15.&lt;CreateAction&gt;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)

[BrianFleming]

What data sources are you trying to connect to?

What types of fields have you got in the data?