Microsoft Excel — 1 Essential PowerQuery Design Step

If you “Load Data” Into an Excel Worksheet — Do This

Everytime you “Refresh” a Query through PowerQuery, the default setting is to adjust the column width of the table each time it updates.

This becomes painful if you modify the table for presentation purposes (wrap text columns, etc.). Many times, I turn this setting off. Here’s how:

Activate Query (Select cell inside Table)

  • Access Query ToolsDesign tab → Select Properties
PowerQuery — Select Properties Menu Option

Modify Query Properties

  • Uncheck “Adjust column width” box
PowerQuery “Default” is to automatically adjust column widths on Refresh
Uncheck “Adjust column width” setting and click “OK”

That’s it. Now, each time you refresh your query, the column widths will stay as you manually set them.

Automate It!

If you use PowerQuery frequently (I do), it’s worth automating this switch (both the OFF and the ON activation).

Here is the VBA in a .txt file format. Just copy the code into your personal macro workbook (personal.xlsb)!

If you haven’t set up a personal macro workbook, now is a great time to do it!

About Don

“It’s time for different”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

I frequently get this question. My response (it’s not what you think!)? Check it out here!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.