Table cells shrink unwanted

%3CLINGO-SUB%20id%3D%22lingo-sub-2258951%22%20slang%3D%22en-US%22%3ETable%20cells%20shrink%20unwanted%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2258951%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20colleagues%3C%2FP%3E%3CP%3EI%20have%20a%20power%20query%20with%20an%20output%20to%20an%20excel%20table.%20After%20a%20power%20query%20refresh%2C%20the%20cell%20size%20is%20automatically%20updated%20to%20the%20largest%20value.%20Therefore%2C%20the%20checkbox%20%E2%80%9Cadjust%20column%20width%E2%80%9D%20is%20enabled.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Zdenek_Moravec_0-1617806484454.png%22%20style%3D%22width%3A%20462px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F270763i4E7FBD5A2F61683D%2Fimage-dimensions%2F462x278%3Fv%3Dv2%22%20width%3D%22462%22%20height%3D%22278%22%20role%3D%22button%22%20title%3D%22Zdenek_Moravec_0-1617806484454.png%22%20alt%3D%22Zdenek_Moravec_0-1617806484454.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20open%20the%20workbook%20a%20next%20day%20and%20the%20cells%20are%20shrinked%20to%20the%20default%20width!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Zdenek_Moravec_1-1617806530219.png%22%20style%3D%22width%3A%20447px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F270764i411C50FE6BFB3748%2Fimage-dimensions%2F447x428%3Fv%3Dv2%22%20width%3D%22447%22%20height%3D%22428%22%20role%3D%22button%22%20title%3D%22Zdenek_Moravec_1-1617806530219.png%22%20alt%3D%22Zdenek_Moravec_1-1617806530219.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20a%20power%20query%20refresh%2C%20the%20cells%20size%20is%20automatically%20updated%20to%20the%20largest%20value%20(as%20same%20as%20first%20picture).%3C%2FP%3E%3CP%3EWhy%20the%20cells%20are%20shrinked%20without%20any%20reason%3F%20This%20behavior%20does%20not%20happen%20every%20time%20and%20I%20cannot%20reproduce%20the%20issue.%20Is%20it%20a%20bug%20or%20a%20feature%3F%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3CP%3EZdenek%20Moravec%3C%2FP%3E%3CP%3ECesky%20Krumlov%2C%20Czech%20Republic%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2258951%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

Dear colleagues

I have a power query with an output to an excel table. After a power query refresh, the cell size is automatically updated to the largest value. Therefore, the checkbox “adjust column width” is enabled.

Zdenek_Moravec_0-1617806484454.png

I open the workbook a next day and the cells are shrinked to the default width!

Zdenek_Moravec_1-1617806530219.png

 

After a power query refresh, the cells size is automatically updated to the largest value (as same as first picture).

Why the cells are shrinked without any reason? This behavior does not happen every time and I cannot reproduce the issue. Is it a bug or a feature?

Thank You

Zdenek Moravec

Cesky Krumlov, Czech Republic

3 Replies

@Zdenek_Moravec 

Zdenek, if you uncheck "Adjust column width" column width will be as you set it manually (without changing), otherwise it changes it based on some logic in background. 

@Sergei Baklan

I was thinking of this solution. The disadvantage is, that when new data come and they are very wide, they do not fit to my manually adjusted columns. Anyway I hope it is less devil for the customer then the shrinked cells.

Next workaround was to set the option “Refresh data when opening the file”. Because refresh query sets the columns width properly. But the "shrinked cells" issue appeared sometimes again. So it means, that the query was not refreshed after opening the workbook ... really strange. Is there any situation, that the query will not refresh despite the checkbox “Refresh data when opening the file”?

@Zdenek_Moravec 

About refresh - if you checked refresh on opening it shall be refreshed. If that doesn't affect columns that doesn't mean query is not refreshed.