none
The PowerQuery table/link remains despite being removed. How do I get it to go away? RRS feed

  • Question

  • I have a PowerQuery loading an alternative-table into
    a calculation tool we use. When creating a customer version I have a macro I
    remove all internal tabs with including the Alternative-tab. I have tried to
    include the following pieces in the macro to really ensure the link is broken
    but still when I re-open the customer version of the file the be
    low screen pops
    up – it re-creates a tab and then tries to load the query even though I have
    broken the link and removed the table before the macro finalizes. I have tried
    below two sections of the macro separate from each other but it still do not
    work.

    How do I get the link/table/query to go away and not try to re-load in the customer file? 

    Macro-text used (part of a bigger one):

    Sheets("Alternativ").Select

    ActiveSheet.ListObjects("Alternativ1").Unlink

    Sheets("Alternativ").Select

    Range("Alternativ1[[#Headers],[Item code]]").Select

    Selection.Clear





    • Edited by KimSweden Wednesday, July 30, 2014 7:24 AM Wrong linebreak changed
    • Moved by George123345 Thursday, July 31, 2014 7:03 AM
    • Moved by George Hua Friday, August 1, 2014 6:54 AM PowerQuery related
    Wednesday, July 30, 2014 7:15 AM

Answers

  • There are two ways (that I am aware of) that will allow you to "break" the link from a Power Query source:

    1) Copy/paste the table (the original ListObject) to another location in the workbook, and then delete the original  table.

    2) Change the table source in M to refer to something else (say, back to itself), so that Power Query cannot find a real source from which to update. In this case, Power Query simply retains the original data.

    Of course, you shouldn't have to do either of the above. Power Query should simply turn the query table into a standard, non-refreshable table after you click Unlink (which currently provides a false indication that the table is unlinked).
    Sunday, August 3, 2014 9:44 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support

    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Thursday, July 31, 2014 7:04 AM
  • Hi Kim,

    Do you want to unlink the table?

    If you unlink the table from ribbon:

    What happened?

    Do you get the same result after re-opening this workbook?

    In addition, you could record a macro to get VBA code as you want.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Edited by George Hua Friday, August 1, 2014 3:25 AM
    Friday, August 1, 2014 3:22 AM
  • Hi and thank you for the reply, what you show there is what I got in the macro (which is recorded macro) as this part:

    Sheets("Alternativ").Select

    ActiveSheet.ListObjects("Alternativ1").Unlink

    So that is something I already tried and it is the same result - the PowerQuery tries to re-load the table again when I re-open the cleared file.

    Friday, August 1, 2014 5:34 AM
  • Hi Kim,

    Since the result is same, the issue is more related to PowerQuery feature.

    I have moved this thread to PowerQuery forum for more effective responses.

    Thanks for your understanding.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 1, 2014 6:55 AM
  • So you want to remove a query?

    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Saturday, August 2, 2014 2:23 AM
    Owner
  • If I understand the question correctly, you want to programmatically remove a Power Query query from a workbook. Is that right? If so, this isn't something that's supported today.
    Sunday, August 3, 2014 3:15 PM
  • There are two ways (that I am aware of) that will allow you to "break" the link from a Power Query source:

    1) Copy/paste the table (the original ListObject) to another location in the workbook, and then delete the original  table.

    2) Change the table source in M to refer to something else (say, back to itself), so that Power Query cannot find a real source from which to update. In this case, Power Query simply retains the original data.

    Of course, you shouldn't have to do either of the above. Power Query should simply turn the query table into a standard, non-refreshable table after you click Unlink (which currently provides a false indication that the table is unlinked).
    Sunday, August 3, 2014 9:44 PM
  • Hi Ed, thank you for the reply. Yes, in the final customer file I would like it to be removed so that it not tries to re-load the table in the customer document when re-opening it after deleting all the internal tabs of the calculation file.

    Wednesday, August 6, 2014 5:16 AM
  • Hi and thank you for the suggested solutions. I have been trying them out but I am not sure if I understand correctly or do it right either one of them. Could you please eloborate on the steps to take if possible please, it would be of great help.

    Solution 1) When you write original ListObject, do you mean that the table in the source file should be moved or the table that imports the data into the calculation file?

    Solution 2) I tried to change the table source but I am not sure what you mean by "in M", is M an abbreviation of something? I probably do not have the right knowledge about computer programming language to understand. I tried this place below but I could not get it to work. I also tried the Unlink datasource just below but it also did not work:


    • Edited by KimSweden Wednesday, August 6, 2014 5:32 AM First picture failed to load into the message
    Wednesday, August 6, 2014 5:29 AM
  • For (1), do the following:

    a) Click a cell in the header row of the table with the query that you wish to unlink.

    b) Press Ctrl+A (or Ctrl+Shift+Spacebar) to select the entire table

    (c) Press Ctrl+C to copy the table

    (d) Select another worksheet for pasting the table data

    (e) Paste values by whatever means you prefer (Ribbon, right-click context menu...). Note: after you paste values, you won't have a table - just the values in worksheet cells

    (f) If you want to convert the copied range to a table, click anywhere in the range, and on the Ribbon, choose Insert-->Table

    M is the name of the language that Power Query uses to generate query steps, and that allows you to build queries without using the functionality included the Power Query UI (except for the advanced editor). If you're not heard of M, then stick with the above procedure.


    • Edited by Colin Banfield Wednesday, August 6, 2014 5:13 PM Modify step
    Wednesday, August 6, 2014 4:53 PM
  • Hi,

    Thank you very much for the input. It took a long time to write back, sorry for that, as work got going after vacation and things got very busy.

    I have tried the above suggestion now in a couple of different ways with my file. It does work when you manually do the things you list - then the table does not try to reload in the customer file.

    However it does not work when I have the macro do the same thing. Then the link remains and the customer file try to reload the table by re-creating a tab and trying (but failing) to load the table there with the result shown in the initial post. I created the additional macro text by recording it in a different workbook and then moving it into the big macro that changes the whole file into a customer version. The macro itself works and no problems there, the end result is the customer file looking good - it is just that the table tries to reload itself.

    The macro-text used for the above operation looks like this now:

    Sheets("Alternativ").Select
        Range("Alternativ1[#All]").Select
        Selection.Copy
        Sheets("Remove Alt tab").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Alternativ").Select
        Range("Alternativ1[#All]").Select
        Range("Alternativ1[[#Headers],[UOM]]").Activate
        Application.CutCopyMode = False
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Selection.ListObject.ListColumns(1).Delete
        Sheets(Array("Alternativ", "Remove Alt tab", "Varugrupper", "PDM", "Prisfil", "Statistik", "Corelist", _
            "Explanation", "Margin Overview", "Increase-Savings Sheet")).Select
        Sheets("Increase-Savings Sheet").Activate
        ActiveWindow.SelectedSheets.Delete

    Wednesday, August 27, 2014 5:16 PM
  • Hi Kim, try the following code:

    Public Const IsDEBUGMODE As Boolean = False
    Sub CopyQueryTableDataToNewSheet()

        On Error GoTo Catch
       
        Application.DisplayAlerts = False
        Dim InitialActiveSheet As Worksheet: Set InitialActiveSheet = ActiveSheet
        Dim TableRange As Range: Set TableRange = InitialActiveSheet.ListObjects(1).Range
        Dim NewSheet As Worksheet: Set NewSheet = Sheets.Add(After:=ActiveSheet)
        Dim TableRangeTopLeftCellAddress As String
        TableRangeTopLeftCellAddress = TableRange.Cells(1).Address(0, 0)
        TableRange.Copy
        With NewSheet
            .Range(TableRangeTopLeftCellAddress).PasteSpecial Paste:=xlPasteValues
            .ListObjects.Add(xlSrcRange, Range(TableRange.Address), , xlYes).Name = "Non_Connection_Table"
            .Range(TableRangeTopLeftCellAddress).Select
        End With
        InitialActiveSheet.Delete
       
    Finally:
        Application.DisplayAlerts = True
        Exit Sub

    Catch:
        MsgBox Prompt:=Err.Number & ":  " & Err.Description
        If IsDEBUGMODE Then
            Stop
            Resume
        Else
            Resume Finally
        End If
       
    End Sub

    Note: The highlighted line was wrapped when I pasted the code here, but should be on one line.
    • Edited by Colin Banfield Sunday, September 7, 2014 2:31 AM Add additional detail
    Sunday, September 7, 2014 2:15 AM
  • Hi Colin

    Thank you for providing help and suggestions. I am not sure if I understand this correctly - I am not so experienced in writing my own macro but mainly use recording so I am sure I am inserting this code into the macro in the wrong way and that is why I cannot get it to work.

    But what I tried now is that I copy the whole code as you wrote it from Public Constant to End Sub and pasted it over the code I had listed above after Sheets("Alternativ").Select. The result is that the macro does not even start but the pre-check stops on "Public Constant" with the message "Compile error: Invalid attribute in Sub or Function". I am trying to learn but I am fairly new at this so thanks again for trying to help out. See attached screenshot for details on what it looks like:

    Monday, September 15, 2014 3:27 PM
  • Either delete all of your code and paste the code I provided in the module, or create a new module and paste the code there. You're getting the error because there's code above mine that should be within a Sub or Function. Also, instead of the End Sub that should be the last line in my code, there's some sheet array line that I don't recognize. The missing End Sub will also generate a compile error.

    Monday, September 15, 2014 4:21 PM
  • Hi Colin, thank you for helping - I am trying to keep up :)

    The code you saw above and below is the rest of the macro, creating the customer version of the file contains a lot of more "cleaning work" that the macro is doing from the internal version of the file. I have written in Sub and End Sub to isolate the three different pieces (my first part, your middle part, my final part). Now it looks like furthest below and give me the following error message:

     


    • Edited by KimSweden Friday, September 19, 2014 5:35 AM Misspelled and misplaced text
    Friday, September 19, 2014 5:34 AM
  • Hi Kim, I should apologize for not being clearer. I take things for granted without taking account that you have no familiarity with VBA beyond recording macros.

    In this instance, the problem is that the line Public Const IsDEBUGMODE As Boolean = False is a global constant (don't bother if you don't know what that is). A global constant must appear at the top of the module before your first Sub statement. In hindsight, I regret bothering with any of this stuff. The point of using the constant is you help with troubleshooting if an error occurs when you run the routine.

    If you still have problems with this, delete the Public Const...line, and change the code in the Catch block to read

    Catch:
        MsgBox Prompt:=Err.Number & ":  " & Err.Description
        Resume Finally

    If you still have problems, provide me your email address so that we can take the discussion out of this forum. We have been discussing VBA related issues now for many posts, and none of it is directly related to Power Query.



    Friday, September 19, 2014 2:56 PM