none
Query Table - Column Width Resizing RRS feed

  • Question

  • Hi guys

    I have a Power Query script loading to a table inside Excel.  Unfortunately, it resizes the column width every single time it refreshes.  

    The only way to stop this is to tick "Preserve column sort/filter/layout" inside the tables External Data Properties.  This does not work for my situation because the Power Query script must control the sort order of the columns, not excel. 

    Ticking or unticking "Adjust column width" in External Data Properties does absolutely nothing.

    Attempting to use "AdjustColumnWidth = False" in VBA also does absolutely nothing (I assume it's the same flag as above):

    Sub Refresh_Table()
        With Sheet3.ListObjects("tbl_Hours_Setup").QueryTable
            .AdjustColumnWidth = False
            .Refresh
        End With    
    End Sub

    Does anyone have a solution?

    Wednesday, December 16, 2015 2:52 PM

Answers

All replies

  • Hi Simon,

    Workaround is to create the table-output via:

    Get External Data - Get Data using an existing connection - then select your query.

    Then the unticking "Adjust column width" does it's job.


    Imke Feldmann TheBIccountant.com

    Wednesday, December 16, 2015 3:26 PM
    Moderator
  • Hi Imke

    Unfortunately I'm still experiencing the same issue.  These are the settings I have selected:

    

    Wednesday, December 16, 2015 3:39 PM
  • strange - works fine for me: Excel 2013, 64 bit, current PQ Version (27)

    with Data - refresh all as well as with individual refresh


    Imke Feldmann TheBIccountant.com

    Wednesday, December 16, 2015 3:45 PM
    Moderator
  • Interesting, I have the same setup.... I'll try disabling add-ins and some other odd items.

    The table is self-referential in the PQ script, perhaps that has something to do with it? i.e. the source and output for the script are the same table.

    • Edited by Simon Nuss Wednesday, December 16, 2015 3:48 PM
    Wednesday, December 16, 2015 3:48 PM
  • Also works with self-reference here - so might actually be other settings...


    Imke Feldmann TheBIccountant.com


    Wednesday, December 16, 2015 4:04 PM
    Moderator
  • Yes, you are correct. Self-referential tables are a nifty technique that I use where the Power Query table is an input table (for some other process) and the input must be maintained during a refresh, i.e. it must read the input from itself, perform transformations, write back to itself.

    I'll post an article about them shortly because, as you figured out, they're difficult to initially setup.

    I hope the below scenario answers your questions:

    • There is an Employee table with 1 column: [Employee Names]
    • There is a Project table with 1 column: [Project Names]
    • The goal: make the Project table horizontally expand/contract with [Employee Names] so the user can enter in staff hours, e.g. if 5 employee records exist in [Employee Names] then the Project Table will be 6 columns wide.

    At a high level, my script will:

    1. Read from the Project table
    2. Right join it the Employee table (effectively adding/subtracting columns)
    3. <enter transformations here>
    4. Write to the Project table

    All hours previously entered (1) are still present in (4).


    • Edited by Simon Nuss Wednesday, December 16, 2015 4:55 PM
    Wednesday, December 16, 2015 4:53 PM
  • When I find my time I'll try to upload a workbook with the issue.
    Wednesday, December 16, 2015 4:59 PM
  • I've uploaded my testfile here: https://onedrive.live.com/edit.aspx?cid=de165ddf5d02daff&page=view&resid=DE165DDF5D02DAFF!18972&parId=DE165DDF5D02DAFF!107&app=Excel

    So you can check whether it's with the file or your settings (blue table).

    I find self-reference very useful as well, but currently only prototyping solutions with them, not running them at clients yet. Do they run stable in your environment?


    Imke Feldmann TheBIccountant.com

    Thursday, December 17, 2015 7:08 AM
    Moderator