none
Unexpected Error - Specified cast is not valid RRS feed

  • Question

  • Hello,

    I have a macro that opens and refreshes listed Excel Files with PQ queries.

    Often (but not always) an error is thrown when refreshing PQ table with VBA = ListObject.Refresh causes this error. That means I cannot 100% reproduce the error. Also the file that throws error is different sometimes.

    Excel version: 1906 (Build 11727.20230)

    Recently I had a similar problem described here: https://social.technet.microsoft.com/Forums/en-US/b3185afb-5d0a-4f3f-b70a-59d130890742/unexpected-error-could-not-find-a-packagesession-for-the-given-sessionid?forum=powerquery

    But the error description is different this time.

    The problem is that macro stops and error message has to be closed. (in previous problem the macro/PQ refresh continued). This is very irritating because this is a part of "DHW" refresh procedures that should be run automatically every morning.

    Can you tell from the error dump what is actually the problem?

    Here is the error dump:

    Feedback Type:

    Frown (Error)

    Error Message:

    Specified cast is not valid.

    Stack Trace:

       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)

       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.TryInvokeHostedScript[T](String methodName, T& result, Object[] args)

       at Microsoft.Mashup.Client.Excel.WorkbookQueriesNavigatorHost.GetScrollTop()

       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.OnQueriesContentChangeStarted(Object sender, EventArgs e)

       at Microsoft.Mashup.Host.Models.EventHandlerExtensions.RaiseEvent(EventHandler handler, Object sender)

       at Microsoft.Mashup.Host.Document.Model.Queries.ChangeScope..ctor(Queries queries, PackagePath packagePath, PackageReference packageReference, Boolean commitEachEdit)

       at Microsoft.Mashup.Host.Document.Model.Queries.CreateChangeScope(Boolean commitEachEdit)

       at Microsoft.Mashup.Client.UI.Shared.Model.QueriesUtilities.ForEachWithChangeScope[T](IEnumerable`1 items, Func`2 getQueries, Action`1 action)

       at Microsoft.Mashup.Client.Excel.Fill.FillManager.<>c__DisplayClass72_0.<ForEachFillSessionByWorkbook>b__1()

       at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)

       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)

       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbookIdentity workbookIdentity, UndoableActionType actionType, Action action)

       at Microsoft.Mashup.Client.Excel.Fill.FillManager.ForEachFillSessionByWorkbook(Action`1 action)

       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.UpdateQueriesProgress()

       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.<OnFillUpdateTimerTick>b__10_0()

       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

    Stack Trace Message:

    Specified cast is not valid.

    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.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)

       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__DisplayClass14_0.<HandleException>b__0()

       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)

       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)

       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.OnFillUpdateTimerTick(Object sender, EventArgs eventArgs)

       at System.Windows.Forms.Timer.OnTick(EventArgs e)

       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)

       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

       at System.StubHelpers.InterfaceMarshaler.ConvertToManaged(IntPtr pUnk, IntPtr itfMT, IntPtr classMT, Int32 flags)

       at System.Windows.Forms.UnsafeNativeMethods.IHTMLDocument2.GetLocation()

       at System.Windows.Forms.WebBrowser.get_Document()

       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.Microsoft.Mashup.Client.UI.Shared.IHostedWebBrowser.get_Document()

       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.get_CanInvoke()

       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)

       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke(String script)

       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.RefreshView()

       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.Refresh()

       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.LoadFromQueries(Queries queries)

       at Microsoft.Mashup.Client.Excel.TaskPaneControls.QueriesTaskPaneControlBase.SetActiveWorkbookQueries(Queries queries)

       at Microsoft.Mashup.Client.Excel.TaskPaneControls.QueriesTaskPaneControlBase.OnWorkbookPackageCreated(IWorkbook workbook, WorkbookPackage workbookPackage)

       at System.Action`2.Invoke(T1 arg1, T2 arg2)

       at Microsoft.Mashup.Client.Excel.PackageManager.RaiseWorkbookPackageCreatedEvent(IWorkbook workbook, WorkbookPackage workbookPackage)

       at Microsoft.Mashup.Client.Excel.PackageManager.InitializeWorkbookPackageInfo(IWorkbook workbook, IWorkbookIdentity workbookIdentity, Boolean allowCreateNew, Boolean reloadingFromWorkbook)

       at Microsoft.Mashup.Client.Excel.PackageManager.EnsureWorkbookPackageInfo(IWorkbook workbook, Boolean allowCreateNew, Boolean reloadingPackage)

       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CacheEntry.GetWorkbookQueries(IWindowContext windowContext, PackageManager packageManager)

       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CacheEntry.Create(IWindowContext windowContext, IWorkbookServices workbookServices, PackageManager packageManager)

       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CreateAndSetCacheEntry(IWindowContext windowContext)

       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.GetOrCreateCacheEntry(IWindowContext windowContext)

       at Microsoft.Mashup.Client.Excel.CustomTaskPaneManagers.UpdateQueriesTaskPaneVisibility(IWindowContext windowContext)

       at Microsoft.Mashup.Client.Excel.ExcelEventHandler.PerformPeriodicUpdate(IEnumerable`1 queriesTaskPaneVisibilityUpdateWindows)

       at Microsoft.Mashup.Client.Excel.NativeEventHandler.<OnIdle>b__11_0()

       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

       at Microsoft.Mashup.Client.Excel.NativeEventHandler.OnIdle()

       at Microsoft.Mashup.Client.Excel.NativeExcelConnectorBase.<OnIdleCallback>b__43_0()

       at Microsoft.Mashup.Client.Excel.Native.NativeExcelCallbackInvoker.InvokeAndReturnHResult(Action action)

       at Microsoft.Mashup.Client.Excel.NativeExcelConnectorBase.OnIdleCallback()

    Supports Premium Content:

    True

    Formulas:

    section Section1;

    shared DOHODY = let

        Source = Folder.Files("G:\HR CR_SK\HR IT\Sensitive\NUGGET"),

        #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "TESTMHDP.CSV") and ([Folder Path] <> "G:\HR CR_SK\HR IT\Sensitive\NUGGET\MZDY100\")),

        #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),

        #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from NUGGET", each #"Transform File from NUGGET"([Content])),

        #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

        #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Folder Path", "Transform File from NUGGET"}),

        #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from NUGGET", Table.ColumnNames(#"Transform File from NUGGET"(#"Sample File"))),

        #"Added Custom" = Table.AddColumn(#"Expanded Table Column1", "PODNIK", each Text.End( List.Last( List.RemoveNulls( List.RemoveMatchingItems ( Text.Split([Folder Path],"\"),{""}))),1)),

        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Folder Path"}),

        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"PODNIK", "Osobní číslo", "měsíc", "rok", "Jméno", "Vynětí z ES", "FTE", "Smlouva", "Důvod vynětí", "Datum ukončení", "Důvod ukončení praco", "Návrat do ES", "Entita", "Věk", "Pohlaví", "Profese", "Úsek", "Grade", "Datum nástupu", "Důvod nástupu", "První nástup", "Plán. ukonč. PP", "Nadřízený", "Level", "Datum ukonč. ZD", "Oddělení", "VPP", "Základní měsíční pla", "Oddělní", "Profese(funkce)", "Litujeme", "Nadřízený_1", "L5081", "Důvod ukončení praco_2", "Hodinová sazba (tari"}),

        #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"PODNIK", Int64.Type}, {"Osobní číslo", Int64.Type}, {"měsíc", Int64.Type}, {"rok", Int64.Type}, {"Jméno", type text}, {"Vynětí z ES", type date}, {"FTE", type number}, {"Smlouva", type text}, {"Důvod vynětí", type text}, {"Datum ukončení", type date}, {"Důvod ukončení praco", Int64.Type}, {"Návrat do ES", type date}, {"Entita", Int64.Type}, {"Věk", type number}, {"Pohlaví", type text}, {"Profese", type text}, {"Úsek", type text}, {"Grade", type text}, {"Datum nástupu", type date}, {"Důvod nástupu", Int64.Type}, {"První nástup", type date}, {"Plán. ukonč. PP", type date}, {"Nadřízený", Int64.Type}, {"Level", Int64.Type}, {"Datum ukonč. ZD", type date}, {"Oddělení", type text}, {"VPP", Int64.Type}, {"Základní měsíční pla", type number}, {"Oddělní", Int64.Type}, {"Profese(funkce)", Int64.Type}, {"Litujeme", type text}, {"Nadřízený_1", type text}, {"L5081", type text}, {"Důvod ukončení praco_2", type text}, {"Hodinová sazba (tari", type number}, {"Druh pracovněprávníh", type number}}),

        #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"PODNIK"},ENTITY,{"Podnik"},"ENTITY",JoinKind.LeftOuter),

        #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Entita", "Stred"}}),

        #"Expanded ENTITY" = Table.ExpandTableColumn(#"Renamed Columns", "ENTITY", {"Entita"}, {"Entita"}),

        #"Reordered Columns1" = Table.ReorderColumns(#"Expanded ENTITY",{"PODNIK", "Entita", "Osobní číslo", "měsíc", "rok", "Jméno", "Vynětí z ES", "FTE", "Smlouva", "Důvod vynětí", "Datum ukončení", "Důvod ukončení praco", "Návrat do ES", "Stred", "Věk", "Pohlaví", "Profese", "Úsek", "Grade", "Datum nástupu", "Důvod nástupu", "První nástup", "Plán. ukonč. PP", "Nadřízený", "Level", "Datum ukonč. ZD", "Oddělení", "VPP", "Základní měsíční pla", "Oddělní", "Profese(funkce)", "Litujeme", "Nadřízený_1", "L5081", "Důvod ukončení praco_2", "Hodinová sazba (tari"}),

        #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns1",{{"Osobní číslo", "ID"}, {"měsíc", "mesic"}, {"Jméno", "jmeno"}, {"Důvod vynětí", "vyneti"}, {"Datum ukončení", "datumKonec"}, {"Důvod ukončení praco", "duvodKonecID"}, {"Návrat do ES", "datumNavratES"}, {"Úsek", "usek"}, {"Grade", "grade"}, {"Datum nástupu", "datumNastup"}, {"Důvod nástupu", "duvodNastup"}, {"Nadřízený", "mngID"}, {"Oddělení", "oddeleni"}, {"Profese(funkce)", "idPos"}, {"Nadřízený_1", "mngJmeno"}, {"Důvod ukončení praco_2", "duvodKonec"}, {"Hodinová sazba (tari", "sazba"}}),

        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns2", each [datumKonec] = null or (Date.StartOfMonth([datumKonec]) >= #date([rok], [mesic], 1))),

        #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Stred] <> 621) ),

        #"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"ID", Order.Ascending}, {"rok", Order.Ascending}, {"mesic", Order.Ascending}}),

        #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "obdobi", each #date([rok],[mesic],1), type date),

        #"Merged Queries1" = Table.NestedJoin(#"Added Custom1", {"ID"}, EMP, {"ID"}, "EMP", JoinKind.LeftOuter),

        #"Expanded EMP" = Table.ExpandTableColumn(#"Merged Queries1", "EMP", {"corpKey"}, {"CK"}),

        #"Merged Queries2" = Table.NestedJoin(#"Expanded EMP", {"mngID"}, EMP, {"ID"}, "EMP", JoinKind.LeftOuter),

        #"Expanded EMP1" = Table.ExpandTableColumn(#"Merged Queries2", "EMP", {"corpKey"}, {"mngCK"}),

        #"Reordered Columns2" = Table.ReorderColumns(#"Expanded EMP1",{"PODNIK", "Entita", "ID", "mesic", "rok", "jmeno", "Vynětí z ES", "FTE", "Smlouva", "vyneti", "datumKonec", "duvodKonecID", "datumNavratES", "Stred", "Věk", "Pohlaví", "Profese", "usek", "grade", "datumNastup", "duvodNastup", "První nástup", "Plán. ukonč. PP", "mngID", "Level", "Datum ukonč. ZD", "oddeleni", "VPP", "Základní měsíční pla", "Oddělní", "idPos", "Litujeme", "mngJmeno", "L5081", "duvodKonec", "sazba", "obdobi", "CK", "mngCK", "Druh pracovněprávníh"}),

        #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns2",{{"Druh pracovněprávníh", "SmlouvaDruh"}})

    in

        #"Renamed Columns3";

    shared ENTITY = let

        Source = Access.Database(File.Contents("G:\HR CR_SK\HR IT\Sensitive\DWH\DWH.accdb"), [CreateNavigationProperties=true]),

        _ENTITY = Source{[Schema="",Item="ENTITY"]}[Data]

    in

        _ENTITY;

    shared #"Sample File" = let

        Source = Folder.Files("G:\HR CR_SK\HR IT\Sensitive\NUGGET"),

        #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "TESTMHDP.CSV") and ([Folder Path] <> "G:\HR CR_SK\HR IT\Sensitive\NUGGET\MZDY100\")),

        Navigation1 = #"Filtered Rows"{0}[Content]

    in

        Navigation1;

    shared #"Sample File Parameter1" = #"Sample File" meta [IsParameterQuery=true, BinaryIdentifier=#"Sample File", Type="Binary", IsParameterQueryRequired=true];

    shared #"Transform Sample File from NUGGET" = let

        Source = Csv.Document(#"Sample File Parameter1",[Delimiter=";", Columns=35, Encoding=1250, QuoteStyle=QuoteStyle.None]),

        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])

    in

        #"Promoted Headers";

    [ FunctionQueryBinding = "{""exemplarFormulaName"":""Transform Sample File from NUGGET""}" ]

    shared #"Transform File from NUGGET" = let

        Source = (#"Sample File Parameter1" as binary) => let

            Source = Csv.Document(#"Sample File Parameter1",[Delimiter=";", Columns=35, Encoding=1250, QuoteStyle=QuoteStyle.None]),

            #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])

        in

            #"Promoted Headers"

    in

        Source;

    shared EMP = let

        Source = Access.Database(File.Contents("G:\HR CR_SK\HR IT\Sensitive\DWH\DWH.accdb"), [CreateNavigationProperties=true]),

        _EMPLOYEE = Source{[Schema="",Item="EMPLOYEE"]}[Data],

        #"Removed Other Columns" = Table.SelectColumns(_EMPLOYEE,{"ID", "corpKey"}),

        #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([corpKey] <> ""))

    in

       #"Filtered Rows";



    Thursday, July 11, 2019 8:29 AM

Answers

All replies

  • Update:

    The problem started recently with Office update.

    The Error is thrown with .Refresh VBA line, not while opening the file

    Friday, July 12, 2019 9:05 AM
  • Hi there,

    Would you please submit a frown next time you encounter this error? It will help us to investigate.


    Guy Hunkin - Excel Team

    Sunday, July 14, 2019 3:01 PM
  • Hey

    Can you please let us also know what version of windows are you running, and also what version of .net framework is installed on your machine ?

    Monday, July 15, 2019 9:38 AM
  • Hello Guy Hunkin, I have submitted several frowns with comment "requested via MSDN"
    Tuesday, July 16, 2019 12:56 PM
  • Hello,

    as I do not have admin permissions I had to use cmd commnad to find the version out 

    (dir %windir%\Microsoft.NET\Framework /AD)

    the latest version is v4.0.30319

    Windows version:

    Version 10.0.17134 Build 17134

    Tuesday, July 16, 2019 1:07 PM
  • Thanks, Jakub. We have opened a bug on this issue and we are investigating.


    Guy Hunkin - Excel Team

    Wednesday, July 17, 2019 1:03 PM
  • Hello,

    I am getting another Frown ( I submitted it).

    This new and the original frown prompt are MODAL and must be closed (query loaded successfully after closing), while for example "Could not find a PackageSession for the given sessionID" has a NON-MODAL frown prompt and macros can continue to work.

    Could you make ALL frown prompts NON-MODAL? And make .Refresh VBA command to return error instead of frown prompt that cannot be handled?

    Feedback Type:

    Frown (Error)

    Error Message:

    Unable to get property 'getScrollTop' of undefined or null reference

    Stack Trace:

       at Microsoft.Mashup.Client.UI.Windows.JsErrorHandler.WrapInvokeScript(Func`1 invokeScript)

       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.InvokeScript(String script, Boolean bypassDebugHost)

       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)

       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.TryInvokeHostedScript[T](String methodName, T& result, Object[] args)

       at Microsoft.Mashup.Client.Excel.WorkbookQueriesNavigatorHost.GetScrollTop()

       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.OnQueriesContentChangeStarted(Object sender, EventArgs e)

       at Microsoft.Mashup.Host.Models.EventHandlerExtensions.RaiseEvent(EventHandler handler, Object sender)

       at Microsoft.Mashup.Host.Document.Model.Queries.ChangeScope..ctor(Queries queries, PackagePath packagePath, PackageReference packageReference, Boolean commitEachEdit)

       at Microsoft.Mashup.Host.Document.Model.Queries.CreateChangeScope(Boolean commitEachEdit)

       at Microsoft.Mashup.Client.UI.Shared.Model.QueriesUtilities.ForEachWithChangeScope[T](IEnumerable`1 items, Func`2 getQueries, Action`1 action)

       at Microsoft.Mashup.Client.Excel.Fill.FillManager.<>c__DisplayClass72_0.<ForEachFillSessionByWorkbook>b__1()

       at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)

       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)

       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbookIdentity workbookIdentity, UndoableActionType actionType, Action action)

       at Microsoft.Mashup.Client.Excel.Fill.FillManager.ForEachFillSessionByWorkbook(Action`1 action)

       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.UpdateQueriesProgress()

       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.<OnFillUpdateTimerTick>b__10_0()

       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

    Stack Trace Message:

    Unable to get property 'getScrollTop' of undefined or null reference

    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.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)

       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__DisplayClass14_0.<HandleException>b__0()

       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)

       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)

       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.OnFillUpdateTimerTick(Object sender, EventArgs eventArgs)

       at System.Windows.Forms.Timer.OnTick(EventArgs e)

       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)

       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

       at System.StubHelpers.InterfaceMarshaler.ConvertToManaged(IntPtr pUnk, IntPtr itfMT, IntPtr classMT, Int32 flags)

    InnerException.Stack Trace Message:

    Unable to get property 'getScrollTop' of undefined or null reference

    InnerException.Stack Trace:

    TypeError: Unable to get property 'getScrollTop' of undefined or null reference

       at eval code (eval code:1:1)

    InnerException.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.GetFeedbackValuesFromException(Exception e, String prefix)

       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.CreateAdditionalErrorInfo(Exception e)

       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)

       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)

       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__DisplayClass14_0.<HandleException>b__0()

       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)

       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)

       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.OnFillUpdateTimerTick(Object sender, EventArgs eventArgs)

       at System.Windows.Forms.Timer.OnTick(EventArgs e)

       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)

       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

       at System.StubHelpers.InterfaceMarshaler.ConvertToManaged(IntPtr pUnk, IntPtr itfMT, IntPtr classMT, Int32 flags)

    Supports Premium Content:

    True

    Formulas:

    section Section1;

    shared TEST_RAW = let

        m1 = Table.Buffer(TEST_M1),

        Source = Csv.Document(File.Contents("\\spczfap00012.insim.biz\groups\HR CR_SK\HR IT\Sensitive\NUGGET\MZDY100\TEST_COP.CSV"),[Delimiter=";", Columns=50, Encoding=1250, QuoteStyle=QuoteStyle.None]),

        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Osobní číslo", Int64.Type}, {"E-mail", type text}, {"Datum narození", type date}, {"Sídlo trvalého praco", type text}, {"Pomocné pole 7", type text}, {"Příjmení", type text}, {"Jméno", type text}, {"Státní příslušnost -", type text}, {"Adresa bydliště", type text}, {"Rodné číslo", type text}, {"Druh pracovněprávníh", type text}, {"Trvalé bydliště - čí", type text}, {"Hodinová sazba (tari", type number}, {"Zařazení podle praco", type text}, {"Nadřízený", Int64.Type}, {"Nadřízený_1", type text}, {"Oddělní", Int64.Type}, {"Název oddělení", type text}, {"Datum vzniku pracovn", type date}, {"Rodné jméno", type text}, {"Rodinný stav - čísel", type text}, {"Sídlo trvalého praco_2", type text}, {"Státní příslušnost", type text}, {"Druh vynětí z eviden", type text}, {"Datum ukončení praco", type date}, {"První nástup", type date}, {"Evidenční číslo OP", type text}, {"Datum ukonč. ZD", type date}, {"Zkušební doba v měsí", Int64.Type}, {"Snížená pracovní sch", type text}, {"Druh důchodu pracují", type text}, {"L4012", type date}, {"Přechodné bydliště", type text}, {"Datum uzavření praco", type date}, {"Úsek_kod", Int64.Type}, {"Úsek", type text}, {"Titul (před)", type text}, {"Titul (za)", type text}, {"Důvod ukončení praco", Int64.Type}, {"VPP", Int64.Type}, {"Profese(funkce)", Int64.Type}, {"Profese(funkce) - čí", type text}, {"Jazyk 1", type text}, {"Jazyk 1_3", type text}, {"Kmenové středisko", Int64.Type}, {"Pracovní středisko", Int64.Type}, {"Externí činnost", Int64.Type}}),

        #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"", "Blank"}}),

        #"Renamed Columns" = Table.RenameColumns(#"Renamed Columns1",{{"Osobní číslo", "ID"}, {"E-mail", "mail"}, {"Datum narození", "DatumNarozeni"}, {"Pomocné pole 7", "corpKey"}, {"Příjmení", "prijmeni"}, {"Jméno", "jmeno"}, {"Státní příslušnost -", "obcanstvi1"}, {"Adresa bydliště", "adresa"}, {"Rodné číslo", "RC"}, {"Nadřízený", "mngID"}, {"Nadřízený_1", "mngJmeno"}, {"Státní příslušnost", "obcanstvi2"}, {"Jazyk 1", "jazyk1"}, {"Jazyk 1_3", "jazyk2"}, {"Předepsané vzdělání_4", "vzdelani1"}, {"Sídlo trvalého praco", "sidloPrac1"}, {"Sídlo trvalého praco_2", "sidloPrac2"}, {"Přechodné bydliště", "adresaPrechodna"}, {"Titul (před)", "titulPred"}, {"Titul (za)", "titulZa"}, {"Předepsané vzdělání", "vzdelani2"}, {"Pracovní středisko", "stredisko"}, {"Profese(funkce) - čí", "profese"}, {"Profese(funkce)", "idPos"}, {"Důvod ukončení praco", "duvodUkonceni"}, {"Úsek", "usek"}, {"Úsek_kod", "usekKod"}, {"Datum uzavření praco", "datumUzavreniPrac"}, {"Druh důchodu pracují", "duchod"}, {"Snížená pracovní sch", "snizenaPrac"}, {"Zkušební doba v měsí", "zkusebniDoba"}, {"Datum ukonč. ZD", "datumZD"}, {"První nástup", "prvniNastup"}, {"Datum ukončení praco", "DatumKonec"}, {"Druh vynětí z eviden", "vyneti"}, {"Rodinný stav - čísel", "rodinnyStav"}, {"Rodné jméno", "jmenoRodne"}, {"Datum vzniku pracovn", "datumVznikuPrac"}, {"Název oddělení", "oddeleni"}, {"Oddělní", "oddeleniKod"}, {"Zařazení podle praco", "Zarazeni"}, {"Hodinová sazba (tari", "sazba"}, {"Trvalé bydliště - čí", "bydlisteCiselnik"}, {"Druh pracovněprávníh", "smlouvaDruh"}, {"Externí činnost", "externiCinnost"}}),

        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Evidenční číslo OP", "snizenaPrac", "duchod", "Kmenové středisko", "sazba"}),

        #"Added Custom" = Table.AddColumn(#"Removed Columns", "RITO", each if [stredisko] > 999 and [usek] = "IT HUB Prague" then 1 else if [stredisko] > 999 and [usek] <> "IT HUB Prague" then 2 else 0, Int64.Type),

        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Region", each if [RITO] = 1 then "IT HUB Prague"

    else if [RITO] = 2 then "SOLVENCY II"

    else if List.Contains(List.Buffer(T_region_IDPOS), [idPos]) then "Region"

    else "Central", type text),

        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "AKTIVNI_MESICplus", each if [DatumKonec] = null or Date.From([DatumKonec]) >=  Date.StartOfMonth( Dnes) then true else false, type logical),

        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "AKTIVNI_DNES", each if ([DatumKonec] = null or Date.From([DatumKonec]) >=  Dnes) and [datumVznikuPrac] <=Dnes  then true else false, type logical),

        #"Added Custom4" = Table.AddColumn(#"Added Custom3", "BUDOUCI", each if  [datumVznikuPrac] = null or [datumVznikuPrac] <=Dnes then false else true, type logical ),

        #"Inserted Merged Column" = Table.Buffer( Table.AddColumn(#"Added Custom4", "IDjoin", each Text.Combine({Text.From([ID], "cs-CZ"), Text.From([VPP], "cs-CZ"), Text.From([stredisko], "cs-CZ")}, "_"), type text)),

        #"Inserted First Characters" = Table.AddColumn(#"Inserted Merged Column", "ENTITA_stred", each Number.From( Text.Start(Text.From([stredisko], "cs-CZ"), 1)), type number),

        #"Merged Queries1" = Table.NestedJoin(#"Inserted First Characters", {"ENTITA_stred"}, ENTITY, {"Stred"}, "ENTITY", JoinKind.LeftOuter),

        #"Expanded ENTITY" = Table.ExpandTableColumn(#"Merged Queries1", "ENTITY", {"Nazev1"}, {"Entita"}),

        #"Merged Queries" = Table.NestedJoin(#"Expanded ENTITY",{"IDjoin"},m1 ,{"IDjoin"},"TEST_M1",JoinKind.LeftOuter),

        #"Expanded TEST_M1" = Table.ExpandTableColumn(#"Merged Queries", "TEST_M1", {"Datum návratu do evi", "Datum vynětí z evide", "Grade", "Plán. ukonč. PP", "FTE", "Číslo telefonu do by", "Číslo zdravotní poji", "Zákonný týdenní prac", "Jiný týdenní pracovn", "Datum poslední preve", "Bydliště v cizině", "Pohlaví", "Adresa bydliště - ul", "Adresa bydliště - ob", "Adresa bydliště - po", "Adresa bydliště - PS", "Stát", "Stát_1", "Závazek k organizaci", "Datum ukončení závaz", "Závazek k organizaci_2", "Datum ukončení závaz_3", "Závazek k organizaci_4", "Datum ukončení závaz_5", "Závazek k organizaci_6", "Datum ukončení závaz_7", "Závazek k organizaci_8", "Datum ukončení závaz_9", "E-mail", "Důvod ukončení praco", "Místo narození", "L3133", "Důvod nástupu", "Důvod nástupu - číse"}, {"Datum návratu do evi", "Datum vynětí z evide", "Grade", "Plán. ukonč. PP", "FTE", "Číslo telefonu do by", "Číslo zdravotní poji", "Zákonný týdenní prac", "Jiný týdenní pracovn", "Datum poslední preve", "Bydliště v cizině", "Pohlaví", "Adresa bydliště - ul", "Adresa bydliště - ob", "Adresa bydliště - po", "Adresa bydliště - PS", "Stát", "Stát_1", "Závazek k organizaci", "Datum ukončení závaz", "Závazek k organizaci_2", "Datum ukončení závaz_3", "Závazek k organizaci_4", "Datum ukončení závaz_5", "Závazek k organizaci_6", "Datum ukončení závaz_7", "Závazek k organizaci_8", "Datum ukončení závaz_9", "E-mail", "Důvod ukončení praco", "Místo narození", "L3133", "Důvod nástupu", "Důvod nástupu - číse"}),

        #"Inserted Text Before Delimiter" = Table.AddColumn(#"Expanded TEST_M1", "Text Before Delimiter", each if Text.Contains([jmeno], " ") = true then Text.BeforeDelimiter([jmeno], " ") else [jmeno], type text),

        #"Merged Queries2" = Table.NestedJoin(#"Inserted Text Before Delimiter", {"Text Before Delimiter", "Pohlaví"}, osloveni, {"Jmeno", "Pohlavi"}, "osloveni", JoinKind.LeftOuter),

        #"Expanded osloveni" = Table.ExpandTableColumn(#"Merged Queries2", "osloveni", {"Osloveni"}, {"Osloveni"}),

        FTE_opravaDohoda0 = Table.AddColumn(#"Expanded osloveni", "FTE2", each if Text.Contains([smlouvaDruh], "dohoda") then 0 else [FTE], type number),

        FTEoprava2 = Table.RemoveColumns(FTE_opravaDohoda0,{"Text Before Delimiter", "FTE"}),

        FTEoprava3 = Table.RenameColumns(FTEoprava2,{{"FTE2", "FTE"}}),

        #"Renamed Columns2" = Table.RenameColumns(FTEoprava3,{{"Datum návratu do evi", "datumNavrat"}, {"Datum vynětí z evide", "datumVyneti"}, {"Plán. ukonč. PP", "datumPLANkonec"}, {"Číslo telefonu do by", "mobil"}, {"Číslo zdravotní poji", "zdravPojistovna"}, {"Zákonný týdenní prac", "UvazekCelkem"}, {"Jiný týdenní pracovn", "UvazekJiny"}, {"Datum poslední preve", "datumPreventProhl"}, {"Bydliště v cizině", "adresaCizina"}, {"Adresa bydliště - ul", "AdrUlice"}, {"Adresa bydliště - ob", "AdrObec"}, {"Adresa bydliště - po", "AdrPosta"}, {"Adresa bydliště - PS", "AdrPSC"}, {"Stát", "AdrStat1"}, {"Stát_1", "AdrStat2"}, {"Závazek k organizaci", "Zavaz1"}, {"Datum ukončení závaz", "Zavaz1Datum"}, {"Závazek k organizaci_2", "Zavaz2"}, {"Datum ukončení závaz_3", "Zavaz2Datum"}, {"Závazek k organizaci_4", "Zavaz3"}, {"Datum ukončení závaz_5", "Zavaz3Datum"}, {"Závazek k organizaci_6", "Zavaz4"}, {"Datum ukončení závaz_7", "Zavaz4Datum"}, {"Závazek k organizaci_8", "Zavaz5"}, {"Datum ukončení závaz_9", "Zavaz5Datum"}, {"E-mail", "mailSoukromy"}, {"Pohlaví", "Pohlavi"}, {"Důvod ukončení praco", "DuvodUkonceniNaz"}, {"Místo narození", "MistoNarozeni"}, {"L3133", "TreximaKod"}, {"Důvod nástupu", "DuvodNastup"}, {"Důvod nástupu - číse", "DuvodNastupNAZ"}}),

        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns2",{"ID", "mail", "DatumNarozeni", "sidloPrac1", "corpKey", "prijmeni", "jmeno", "obcanstvi1", "adresa", "RC", "smlouvaDruh", "bydlisteCiselnik", "Zarazeni", "mngID", "mngJmeno", "oddeleniKod", "oddeleni", "datumVznikuPrac", "jmenoRodne", "rodinnyStav", "sidloPrac2", "obcanstvi2", "vyneti", "DatumKonec", "prvniNastup", "datumZD", "zkusebniDoba", "L4012", "adresaPrechodna", "datumUzavreniPrac", "usekKod", "usek", "titulPred", "titulZa", "duvodUkonceni", "VPP", "idPos", "profese", "jazyk1", "jazyk2", "stredisko", "vzdelani2", "vzdelani1", "externiCinnost", "Blank", "RITO", "Region", "AKTIVNI_MESICplus", "AKTIVNI_DNES", "BUDOUCI", "IDjoin", "ENTITA_stred", "Entita", "datumNavrat", "datumVyneti", "Grade", "datumPLANkonec", "FTE", "mobil", "zdravPojistovna", "UvazekCelkem", "UvazekJiny", "datumPreventProhl", "adresaCizina", "Pohlavi", "AdrUlice", "AdrObec", "AdrPosta", "AdrPSC", "AdrStat1", "AdrStat2", "Zavaz1", "Zavaz1Datum", "Zavaz2", "Zavaz2Datum", "Zavaz3", "Zavaz3Datum", "Zavaz4", "Zavaz4Datum", "Zavaz5", "Zavaz5Datum", "mailSoukromy", "Osloveni", "DuvodUkonceniNaz", "MistoNarozeni", "TreximaKod", "DuvodNastup", "DuvodNastupNAZ"})

    in

        #"Reordered Columns";

    shared EMPLOYEE = let

       Source = Csv.Document(File.Contents("\\spczfap00012.insim.biz\groups\HR CR_SK\HR IT\Sensitive\NUGGET\MZDY100\TEST_COP.CSV"),[Delimiter=";", Columns=50, Encoding=1250, QuoteStyle=QuoteStyle.None]),

        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Osobní číslo", Int64.Type}, {"E-mail", type text}, {"Datum narození", type date}, {"Sídlo trvalého praco", type text}, {"Pomocné pole 7", type text}, {"Příjmení", type text}, {"Jméno", type text}, {"Státní příslušnost -", type text}, {"Adresa bydliště", type text}, {"Rodné číslo", type text}, {"Druh pracovněprávníh", type text}, {"Trvalé bydliště - čí", type text}, {"Hodinová sazba (tari", type number}, {"Zařazení podle praco", type text}, {"Nadřízený", Int64.Type}, {"Nadřízený_1", type text}, {"Oddělní", Int64.Type}, {"Název oddělení", type text}, {"Datum vzniku pracovn", type date}, {"Rodné jméno", type text}, {"Rodinný stav - čísel", type text}, {"Sídlo trvalého praco_2", type text}, {"Státní příslušnost", type text}, {"Druh vynětí z eviden", type text}, {"Datum ukončení praco", type date}, {"První nástup", type date}, {"Evidenční číslo OP", type text}, {"Datum ukonč. ZD", type date}, {"Zkušební doba v měsí", Int64.Type}, {"Snížená pracovní sch", type text}, {"Druh důchodu pracují", type text}, {"L4012", type date}, {"Přechodné bydliště", type text}, {"Datum uzavření praco", type date}, {"Úsek_kod", Int64.Type}, {"Úsek", type text}, {"Titul (před)", type text}, {"Titul (za)", type text}, {"Důvod ukončení praco", Int64.Type}, {"VPP", Int64.Type}, {"Profese(funkce)", Int64.Type}, {"Profese(funkce) - čí", type text}, {"Jazyk 1", type text}, {"Jazyk 1_3", type text}}),

        #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"", "Blank"}}),

        #"Sorted Rows" = Table.Sort(#"Renamed Columns1",{{"Datum vzniku pracovn", Order.Descending}}),

        #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Osobní číslo", "E-mail", "Datum narození", "Sídlo trvalého praco", "Pomocné pole 7", "Příjmení", "Jméno", "Státní příslušnost -", "Adresa bydliště", "Rodné číslo", "Nadřízený", "Nadřízený_1", "Sídlo trvalého praco_2", "Státní příslušnost", "Přechodné bydliště", "Titul (před)", "Titul (za)", "Jazyk 1", "Jazyk 1_3", "Předepsané vzdělání", "Předepsané vzdělání_4"}),

        #"Removed Duplicates" = Table.Distinct(Table.Buffer(#"Removed Other Columns"), {"Osobní číslo"}),

        #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Osobní číslo", "ID"}, {"E-mail", "mail"}, {"Datum narození", "DatumNarozeni"}, {"Pomocné pole 7", "corpKey"}, {"Příjmení", "prijmeni"}, {"Jméno", "jmeno"}, {"Státní příslušnost -", "obcanstvi1"}, {"Adresa bydliště", "adresa"}, {"Rodné číslo", "RC"}, {"Nadřízený", "mngID"}, {"Nadřízený_1", "mngJmeno"}, {"Státní příslušnost", "obcanstvi2"}, {"Jazyk 1", "jazyk1"}, {"Jazyk 1_3", "jazyk2"}, {"Předepsané vzdělání_4", "vzdelani1"}, {"Sídlo trvalého praco", "sidloPrac1"}, {"Sídlo trvalého praco_2", "sidloPrac2"}, {"Přechodné bydliště", "adresaPrechodna"}, {"Titul (před)", "titulPred"}, {"Titul (za)", "titulZa"}, {"Předepsané vzdělání", "vzdelani2"}}),

        #"Sorted Rows1" = Table.Sort(#"Renamed Columns",{{"ID", Order.Ascending}}),

        #"Inserted Text Range" = Table.AddColumn(#"Sorted Rows1", "Pohlavi", each try if Number.From(Text.Middle([RC], 2, 2)) >=50 then "Ž" else "M" otherwise "NA", type text)

    in

        #"Inserted Text Range";

    shared T_region_IDPOS = let

        Source = Excel.CurrentWorkbook(){[Name="T_region_IDPOS"]}[Content],

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"idpos", Int64.Type}, {"Progese", type text}}),

        #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"idpos"}),

        idpos = #"Removed Other Columns"[idpos]

    in

        idpos;

    shared TEST_M1 = let

        Source = Csv.Document(File.Contents("G:\HR CR_SK\HR IT\Sensitive\NUGGET\MZDY100\TEST_M1.CSV"),[Delimiter=";", Columns=37, Encoding=1250, QuoteStyle=QuoteStyle.None]),

        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Osobní číslo", Int64.Type}, {"VPP", Int64.Type}, {"Kmenové středisko", Int64.Type}, {"Datum návratu do evi", type date}, {"Datum vynětí z evide", type date}, {"Grade", type text}, {"Plán. ukonč. PP", type date}, {"FTE", type number}, {"Datum poslední preve", type date}}),

        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Osobní číslo", type text}, {"VPP", type text}, {"Kmenové středisko", type text}}, "cs-CZ"),{"Osobní číslo", "VPP", "Kmenové středisko"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"IDjoin"),

        #"Extracted First Characters" = Table.TransformColumns(#"Merged Columns", {{"Grade", each Text.Select(_, {"0".."9"})}}),

        #"Changed Type1" = Table.TransformColumnTypes(#"Extracted First Characters",{{"Grade", type number}, {"Zákonný týdenní prac", type number}, {"Jiný týdenní pracovn", type number}}),

        #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",0,null,Replacer.ReplaceValue,{"Jiný týdenní pracovn"}),

        #"Replaced Value" = Table.ReplaceValue(#"Replaced Value1","00000","",Replacer.ReplaceText,{"Číslo zdravotní poji"}),

        #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Číslo zdravotní poji", Text.Trim, type text}}),

        #"Changed Type2" = Table.TransformColumnTypes(#"Trimmed Text",{{"Datum ukončení závaz", type date}, {"Datum ukončení závaz_3", type date}, {"Datum ukončení závaz_5", type date}, {"Datum ukončení závaz_7", type date}, {"Datum ukončení závaz_9", type date}, {"Důvod nástupu", type number}})

    in

        #"Changed Type2";

    shared #"Errors in TEST_RAW" = let

    Source = TEST_RAW,

    #"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1),

      #"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"ID", "mail", "DatumNarozeni", "sidloPrac1", "corpKey", "prijmeni", "jmeno", "obcanstvi1", "adresa", "RC", "smlouvaDruh", "bydlisteCiselnik", "Zarazeni", "mngID", "mngJmeno", "oddeleniKod", "oddeleni", "datumVznikuPrac", "jmenoRodne", "rodinnyStav", "sidloPrac2", "obcanstvi2", "vyneti", "DatumKonec", "prvniNastup", "datumZD", "zkusebniDoba", "L4012", "adresaPrechodna", "datumUzavreniPrac", "usekKod", "usek", "titulPred", "titulZa", "duvodUkonceni", "VPP", "idPos", "profese", "jazyk1", "jazyk2", "stredisko", "vzdelani2", "vzdelani1", "externiCinnost", "Blank", "RITO", "Region", "AKTIVNI_MESICplus", "AKTIVNI_DNES", "BUDOUCI", "IDjoin", "datumNavrat", "datumVyneti", "Grade"}),

    #"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "ID", "mail", "DatumNarozeni", "sidloPrac1", "corpKey", "prijmeni", "jmeno", "obcanstvi1", "adresa", "RC", "smlouvaDruh", "bydlisteCiselnik", "Zarazeni", "mngID", "mngJmeno", "oddeleniKod", "oddeleni", "datumVznikuPrac", "jmenoRodne", "rodinnyStav", "sidloPrac2", "obcanstvi2", "vyneti", "DatumKonec", "prvniNastup", "datumZD", "zkusebniDoba", "L4012", "adresaPrechodna", "datumUzavreniPrac", "usekKod", "usek", "titulPred", "titulZa", "duvodUkonceni", "VPP", "idPos", "profese", "jazyk1", "jazyk2", "stredisko", "vzdelani2", "vzdelani1", "externiCinnost", "Blank", "RITO", "Region", "AKTIVNI_MESICplus", "AKTIVNI_DNES", "BUDOUCI", "IDjoin", "datumNavrat", "datumVyneti", "Grade"})

    in

      #"Reordered Columns"

    ;

    shared #"Errors in EMPLOYEE" = let

    Source = EMPLOYEE,

    #"Added Index" = Table.AddIndexColumn(Source, "Row Number" ,1),

      #"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"ID", "mail", "DatumNarozeni", "sidloPrac1", "corpKey", "prijmeni", "jmeno", "obcanstvi1", "adresa", "RC", "mngID", "mngJmeno", "sidloPrac2", "obcanstvi2", "adresaPrechodna", "titulPred", "titulZa", "jazyk1", "jazyk2", "vzdelani2", "vzdelani1", "Pohlavi"}),

    #"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "ID", "mail", "DatumNarozeni", "sidloPrac1", "corpKey", "prijmeni", "jmeno", "obcanstvi1", "adresa", "RC", "mngID", "mngJmeno", "sidloPrac2", "obcanstvi2", "adresaPrechodna", "titulPred", "titulZa", "jazyk1", "jazyk2", "vzdelani2", "vzdelani1", "Pohlavi"})

    in

      #"Reordered Columns"

    ;

    shared ENTITY = let

        Source = Access.Database(File.Contents("G:\HR CR_SK\HR IT\Sensitive\DWH\DWH.accdb"), [CreateNavigationProperties=true]),

        _ENTITY = Source{[Schema="",Item="ENTITY"]}[Data]

    in

        _ENTITY;

    shared osloveni = let

        Source = Excel.Workbook(File.Contents("G:\HR CR_SK\HR IT\Sensitive\DWH\prep\other\osloveni.xlsx"), null, true),

        Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],

        #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Jmeno", type text}, {"Pohlavi", type text}, {"Osloveni", type text}}),

        #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Jmeno", "Pohlavi"})

    in

        #"Removed Duplicates";

    shared Dnes = let

        Source = Date.From( DateTime.LocalNow())

    in

        Source;

    Friday, July 26, 2019 8:37 AM
  • Thanks, Jakub. We have opened a bug on this issue and we are investigating.


    Guy Hunkin - Excel Team

    Any movement on this?

    I do the same as the original poster, but using a PowerShell script instead of VBA, to open/close a folder full of Excel files and refresh all the queries in each workbook. This throws the same error as the OP, "Specified cast not valid", but on a random basis, about 20% of time. I can run the script again, with the same input data, and it will happen on completely different files. The stack trace is almost identical to the original poster.

    Feedback Type:
    Frown (Error)
    
    Error Message:
    Specified cast is not valid.
    
    Stack Trace:
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.TryInvokeHostedScript[T](String methodName, T& result, Object[] args)
       at Microsoft.Mashup.Client.Excel.WorkbookQueriesNavigatorHost.GetScrollTop()
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.OnQueriesContentChangeStarted(Object sender, EventArgs e)
       at Microsoft.Mashup.Host.Models.EventHandlerExtensions.RaiseEvent(EventHandler handler, Object sender)
       at Microsoft.Mashup.Host.Document.Model.Queries.ChangeScope..ctor(Queries queries, PackagePath packagePath, PackageReference packageReference, Boolean commitEachEdit)
       at Microsoft.Mashup.Host.Document.Model.Queries.CreateChangeScope(Boolean commitEachEdit)
       at Microsoft.Mashup.Client.UI.Shared.Model.QueriesUtilities.ForEachWithChangeScope[T](IEnumerable`1 items, Func`2 getQueries, Action`1 action)
       at Microsoft.Mashup.Client.Excel.Fill.FillManager.<>c__DisplayClass11.<ForEachFillSessionByWorkbook>b__b()
       at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbookIdentity workbookIdentity, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Fill.FillManager.ForEachFillSessionByWorkbook(Action`1 action)
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.UpdateQueriesProgress()
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.<OnFillUpdateTimerTick>b__2b()
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
    
    Stack Trace Message:
    Specified cast is not valid.
    
    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)
       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.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.OnFillUpdateTimerTick(Object sender, EventArgs eventArgs)
       at System.Windows.Forms.Timer.OnTick(EventArgs e)
       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
       at System.Windows.Forms.UnsafeNativeMethods.IHTMLLocation.GetHref()
       at System.Windows.Forms.WebBrowser.get_Document()
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.Microsoft.Mashup.Client.UI.Shared.IHostedWebBrowser.get_Document()
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.<>c__DisplayClass4.<InvokeScript>b__3()
       at Microsoft.Mashup.Client.UI.Windows.JsErrorHandler.WrapInvokeScript(Func`1 invokeScript)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.InvokeScript(String script, Boolean bypassDebugHost)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke(String script)
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.RefreshView()
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.Refresh()
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.LoadFromQueries(Queries queries)
       at Microsoft.Mashup.Client.Excel.TaskPaneControls.QueriesTaskPaneControlBase.SetActiveWorkbookQueries(Queries queries)
       at Microsoft.Mashup.Client.Excel.TaskPaneControls.QueriesTaskPaneControlBase.OnWorkbookPackageCreated(IWorkbook workbook, WorkbookPackage workbookPackage)
       at System.Action`2.Invoke(T1 arg1, T2 arg2)
       at Microsoft.Mashup.Client.Excel.PackageManager.RaiseWorkbookPackageCreatedEvent(IWorkbook workbook, WorkbookPackage workbookPackage)
       at Microsoft.Mashup.Client.Excel.PackageManager.InitializeWorkbookPackageInfo(IWorkbook workbook, IWorkbookIdentity workbookIdentity, Boolean allowCreateNew, Boolean reloadingFromWorkbook)
       at Microsoft.Mashup.Client.Excel.PackageManager.EnsureWorkbookPackageInfo(IWorkbook workbook, Boolean allowCreateNew, Boolean reloadingPackage)
       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CacheEntry.GetWorkbookQueries(IWindowContext windowContext, PackageManager packageManager)
       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CacheEntry.Create(IWindowContext windowContext, IWorkbookServices workbookServices, PackageManager packageManager)
       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CreateAndSetCacheEntry(IWindowContext windowContext)
       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.GetOrCreateCacheEntry(IWindowContext windowContext)
       at Microsoft.Mashup.Client.Excel.CustomTaskPaneManagers.UpdateQueriesTaskPaneVisibility(IWindowContext windowContext)
       at Microsoft.Mashup.Client.Excel.ExcelEventHandler.PerformPeriodicUpdate(IEnumerable`1 queriesTaskPaneVisibilityUpdateWindows)
       at Microsoft.Mashup.Client.Excel.NativeEventHandler.<OnIdle>b__0()
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at Microsoft.Mashup.Client.Excel.NativeEventHandler.OnIdle()
       at Microsoft.Mashup.Client.Excel.NativeExcelConnectorBase.<OnIdleCallback>b__3a()
       at Microsoft.Mashup.Client.Excel.ExcelCallbackManager.InvokeAndReturnHResult(Action action)
       at Microsoft.Mashup.Client.Excel.NativeExcelConnectorBase.OnIdleCallback()
    

    Tuesday, August 6, 2019 12:58 AM
  • Hi,

    The issue is still under investigation. I don' have any updates for you yet.


    Guy Hunkin - Excel Team

    Wednesday, August 7, 2019 12:23 PM
  • Hello Javier,

    these days I am not experiencing many errors and frowns.

    But when I did, I was experimenting with Background refresh of ListObjects/Queries and it proved  to be much reliable and frown proof.

    Have a look at this code, it may help you reduce the amount of frowns/errors

    lo.QueryTable.Refresh (True) 'True = background refresh
    Dim timeOut As Date
    timeOut = DateAdd("s", 120, Now) ' timeout setting for X seconds
    
    pokus = 1
    Do While ListObject.QueryTable.Refreshing = True 'change to your LO
    Call WaitSeconds(1) ' see the other code block
    Debug.Print "waited. Remaining time out: " & DateDiff("s", Now, timeOut) & " seconds"
    
            If Now >= timeOut Then
            pokus = pokus + 1 
                Debug.Print "Timeout - restarting refresh, pokus: " & pokus
                lo.QueryTable.CancelRefresh ' restarting the Query refresh
     
                Call WaitSeconds(1)
                timeOut = DateAdd("s", 120, Now)
                ListObject.QueryTable.Refresh (BGrefresh)
            End If
    
    Loop
    

    Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
    
    Public Sub WaitSeconds(intSeconds As Integer)
      ' Comments: Waits for a specified number of seconds
      ' Params  : intSeconds      Number of seconds to wait
      ' Source  : Total Visual SourceBook
    
      On Error GoTo PROC_ERR
    
      Dim datTime As Date
    
      datTime = DateAdd("s", intSeconds, Now)
    
      Do
       ' Yield to other programs (better than using DoEvents which eats up all the CPU cycles)
        Sleep 100
        DoEvents
      Loop Until Now >= datTime
    
    PROC_EXIT:
      Exit Sub
    
    PROC_ERR:
      MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.WaitSeconds"
      Err.Clear
      Resume PROC_EXIT
    End Sub

    Wednesday, August 21, 2019 12:04 PM
  • Hello,

    after a longer time I got another Specified cast is not valid error:

    Feedback Type:
    Frown (Error)
    
    Error Message:
    Specified cast is not valid.
    
    Stack Trace:
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.TryInvokeHostedScript[T](String methodName, T& result, Object[] args)
       at Microsoft.Mashup.Client.Excel.WorkbookQueriesNavigatorHost.GetScrollTop()
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.OnQueriesContentChangeStarted(Object sender, EventArgs e)
       at Microsoft.Mashup.Host.Models.EventHandlerExtensions.RaiseEvent(EventHandler handler, Object sender)
       at Microsoft.Mashup.Host.Document.Model.Queries.ChangeScope..ctor(Queries queries, PackagePath packagePath, PackageReference packageReference, Boolean commitEachEdit)
       at Microsoft.Mashup.Host.Document.Model.Queries.CreateChangeScope(Boolean commitEachEdit)
       at Microsoft.Mashup.Client.UI.Shared.Model.QueriesUtilities.ForEachWithChangeScope[T](IEnumerable`1 items, Func`2 getQueries, Action`1 action)
       at Microsoft.Mashup.Client.Excel.Fill.FillManager.<>c__DisplayClass72_0.<ForEachFillSessionByWorkbook>b__1()
       at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbookIdentity workbookIdentity, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Fill.FillManager.ForEachFillSessionByWorkbook(Action`1 action)
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.UpdateQueriesProgress()
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.<OnFillUpdateTimerTick>b__9_0()
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
    
    Stack Trace Message:
    Specified cast is not valid.
    
    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.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
       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__DisplayClass14_0.<HandleException>b__0()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.OnFillUpdateTimerTick(Object sender, EventArgs eventArgs)
       at System.Windows.Forms.Timer.OnTick(EventArgs e)
       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
       at System.Windows.Forms.UnsafeNativeMethods.IHTMLLocation.GetHref()
       at System.Windows.Forms.WebBrowser.get_Document()
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.Microsoft.Mashup.Client.UI.Shared.IHostedWebBrowser.get_Document()
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryGetBrowserDocument(IHostedWebDocument& document)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.get_CanInvoke()
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke(String script)
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.RefreshView()
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.Refresh()
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.LoadFromQueries(Queries queries)
       at Microsoft.Mashup.Client.Excel.TaskPaneControls.QueriesTaskPaneControlBase.SetActiveWorkbookQueries(Queries queries)
       at Microsoft.Mashup.Client.Excel.TaskPaneControls.QueriesTaskPaneControlBase.OnWorkbookPackageCreated(IWorkbook workbook, WorkbookPackage workbookPackage)
       at System.Action`2.Invoke(T1 arg1, T2 arg2)
       at Microsoft.Mashup.Client.Excel.PackageManager.RaiseWorkbookPackageCreatedEvent(IWorkbook workbook, WorkbookPackage workbookPackage)
       at Microsoft.Mashup.Client.Excel.PackageManager.InitializeWorkbookPackageInfo(IWorkbook workbook, IWorkbookIdentity workbookIdentity, Boolean allowCreateNew, Boolean reloadingFromWorkbook)
       at Microsoft.Mashup.Client.Excel.PackageManager.EnsureWorkbookPackageInfo(IWorkbook workbook, Boolean allowCreateNew, Boolean reloadingPackage)
       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CacheEntry.GetWorkbookQueries(IWindowContext windowContext, PackageManager packageManager)
       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CacheEntry.Create(IWindowContext windowContext, IWorkbookServices workbookServices, PackageManager packageManager)
       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CreateAndSetCacheEntry(IWindowContext windowContext)
       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.GetOrCreateCacheEntry(IWindowContext windowContext)
       at Microsoft.Mashup.Client.Excel.CustomTaskPaneManagers.UpdateQueriesTaskPaneVisibility(IWindowContext windowContext)
       at Microsoft.Mashup.Client.Excel.ExcelEventHandler.PerformPeriodicUpdate(IEnumerable`1 queriesTaskPaneVisibilityUpdateWindows)
       at Microsoft.Mashup.Client.Excel.NativeEventHandler.<OnIdle>b__12_0()
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at Microsoft.Mashup.Client.Excel.NativeEventHandler.OnIdle()
       at Microsoft.Mashup.Client.Excel.NativeExcelConnectorBase.<OnIdleCallback>b__43_0()
       at Microsoft.Mashup.Client.Excel.Native.NativeExcelCallbackInvoker.InvokeAndReturnHResult(Action action)
       at Microsoft.Mashup.Client.Excel.NativeExcelConnectorBase.OnIdleCallback()
    
    
    Supports Premium Content:
    True
    
    Formulas:
    
    
    section Section1;
    
    shared DWHpaths = let
        Source = Csv.Document(File.Contents("G:\HR CR_SK\HR IT\Sensitive\DWH\AUTO\servis\DWHpaths.csv"),[Delimiter=";", Columns=2, Encoding=1250, QuoteStyle=QuoteStyle.None]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = "EXPORTY")),
        Column2 = #"Filtered Rows"{0}[Column2]
    in
        Column2;
    
    shared DWH = let
        Source = Access.Database(File.Contents(DWHpaths), [CreateNavigationProperties=true])
    in
        Source;
    
    shared _TEST_RAW = let
        Source = Access.Database(File.Contents(DWHpaths), [CreateNavigationProperties=true]),
        _TEST_RAW1 = Source{[Schema="",Item="TEST_RAW"]}[Data],
        #"Filtered Rows" = Table.SelectRows(_TEST_RAW1, each ([AKTIVNI_MESICplus] = true) and ([smlouvaDruh] = "pracovní poměr na dobu neurčitou" or [smlouvaDruh] = "pracovní poměr na dobu určitou") and ([vyneti] = "důvod vynětí neexistuje")),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"ID", "mail", "corpKey", "prijmeni", "jmeno", "Entita"}),
        #"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"prijmeni", "jmeno"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"jmeno")
    in
        #"Merged Columns";

    Wednesday, March 4, 2020 9:14 AM
  • Thank you for your feedback.

    We are still working on this item. Unfortunately, we do not have a time estimate for the completion of this investigation.

    We encourage you to keep send us feedbacks in Excel next time you encounter the issue (How to give feedback on Microsoft Office)?

    Thanks,

    Inbar, Excel Team

    Sunday, April 5, 2020 2:53 PM
  • Hello, I always submit the frowns - clicking the button does not visually do anything so I hope it sends the frown report to you correctly. I have already submitted dozens of frowns.

    Warm regards,

    Jakub Dušek

    Wednesday, April 8, 2020 12:50 AM