none
Unable to get property 'getScrollTop' of undefined or null reference RRS feed

  • Question

  • Hello,

    along with these frowns:

    Unexpected Error - Specified cast is not valid

    Unexpected Error - Could not find a PackageSession for the given sessionID

    I am getting this new frown: Unable to get property 'getScrollTop' of undefined or null referenceLike in the cases mentioned before, this frown occur unexpectedly. It stops the VBA macro that refreshes the query and therefore stops the whole daily process, that must then be handled manually.

    I would really appreciate any idea how to avoid this frown.

    I am also wondering what does it exactly says? What is getScrollTop ?

    Warm regards

    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 Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
    
    
    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 Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
    
    
    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"))),
        buffer = Table.Buffer(#"Expanded Table Column1"),
        #"Added Custom" = Table.AddColumn(buffer, "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}, {"Křestní", type text}, {"Příjmení", type text}, {"Plat nekrácený", type number}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"PODNIK"},ENTITY2,{"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"}, EMP2, {"ID"}, "EMP", JoinKind.LeftOuter),
        #"Expanded EMP" = Table.ExpandTableColumn(#"Merged Queries1", "EMP", {"corpKey"}, {"CK"}),
        #"Merged Queries2" = Table.NestedJoin(#"Expanded EMP", {"mngID"}, EMP2, {"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", "Křestní", "Příjmení", "Plat nekrácený"}),
        #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns2",{{"Druh pracovněprávníh", "SmlouvaDruh"}, {"Plat nekrácený", "Plat1FTE"}})
    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=38, Encoding=1250, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Jméno_3", "Křestní"}})
    in
        #"Renamed Columns";
    
    [ 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=38, Encoding=1250, QuoteStyle=QuoteStyle.None]),
            #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
            #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Jméno_3", "Křestní"}})
        in
            #"Renamed Columns"
    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";
    
    shared EMP2 = let
        Source = ( OleDb.Query("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""G:\HR CR_SK\HR IT\Sensitive\DWH\DWH.accdb"";", "select [_].[ID],
        [_].[corpKey]
    from 
    (
        select [ID],
            [corpKey]
        from [EMPLOYEE] as [$Table]
    ) as [_]
    where [_].[corpKey] <> '' or [_].[corpKey] is null"))
    in
        Source
    ;
    
    shared ENTITY2 = let
        Source = ( OleDb.Query("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""G:\HR CR_SK\HR IT\Sensitive\DWH\DWH.accdb"";", "select [$Table].[Podnik] as [Podnik],
        [$Table].[Entita] as [Entita],
        [$Table].[Zkratka] as [Zkratka],
        [$Table].[Stred] as [Stred],
        [$Table].[Nazev1] as [Nazev1],
        [$Table].[Aktivni] as [Aktivni],
        [$Table].[NazevOF] as [NazevOF],
        [$Table].[IC] as [IC],
        [$Table].[adresa] as [adresa],
        [$Table].[Popis] as [Popis],
        [$Table].[mzdovka] as [mzdovka],
        [$Table].[F12] as [F12],
        [$Table].[F13] as [F13],
        [$Table].[F14] as [F14],
        [$Table].[F15] as [F15]
    from [ENTITY] as [$Table]"))
    in
        Source;
    
    shared #"DOHODY (2)" = 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")))
    in
        #"Expanded Table Column1";
    
    shared #"DOHODY (3)" = 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"))),
        buffer = Table.Buffer(#"Expanded Table Column1"),
        #"Added Custom" = Table.AddColumn(buffer, "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"},ENTITY2,{"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"}, EMP2, {"ID"}, "EMP", JoinKind.LeftOuter),
        #"Expanded EMP" = Table.ExpandTableColumn(#"Merged Queries1", "EMP", {"corpKey"}, {"CK"}),
        #"Merged Queries2" = Table.NestedJoin(#"Expanded EMP", {"mngID"}, EMP2, {"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";
    

      

    Wednesday, September 25, 2019 8:34 AM

All replies

  • Any help please?

    What does the frown says?

    Tuesday, October 8, 2019 8:28 AM
  • Bumping this up too. I'm having the same exact issue while trying to automatically refresh a query with VBA and task scheduler.
    Wednesday, October 9, 2019 4:52 PM
  • Hi Jakub,

    We have an open bug for this issue and the team is investigating. I'll keep you posted.


    Guy Hunkin - Excel Team

    Wednesday, October 16, 2019 9:30 AM
  • I have exactly the same issue. Using Excel of O365 of in our company. 70% of my automatic reports are hanging with the above error message an waiting for a manual "Cancel" click. Tried already several things, killing open EXCEL.EXE Processes before as well as Microsoft.Mashup.Container.NetFX40.exe. No luck. Is there no temporary workaround using VBA to prevent this problem for time beeing?
    Wednesday, October 23, 2019 5:41 AM
  • Jakub, _hapi_,

    Could one of you please provide a way to reproduce the issue? This will help us very much with investigating the issue.

    Thanks in advance!

    Thursday, October 24, 2019 11:00 AM
  • Hello,

    unfortunately not, the frown happens only sometimes across different files and queries.

    All I can do is "send a frown" which is what I do.


    This frown prompt is MODAL and must be closed (query loads 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.

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

    Warm regards


    • Edited by jakub dusek Thursday, October 24, 2019 2:38 PM
    Thursday, October 24, 2019 2:37 PM