Excel Power Query Editor icons and text too small or too large

This issue had been bugging me for a while and I finally stumbled on this thread which told me that the same issue has been lingering for many years at this point. I have also added this reply to that thread in case it helps people who are not SU users.

tl;dr: If you experience this issue, close and reopen Excel after disconnecting from or connecting to external monitors.

The long version

I've found that:

a) If I have Excel open before disconnecting the laptop from the other displays, then when I disconnect the laptop from the other displays and then try to use PowerQuery from the already-open instance of Excel, the Power Query Editor will not scale correctly and will appear in the laptop's main display's recommended resolution without scaling (i.e. its too small). If I then close Excel entirely, then reopen Excel and try to use the Power Query Editor again, the Editor will scale correctly.

b) If I have the Power Query editor open while the laptop is disconnected from the other displays, then I connect the laptop to the other displays, the PowerQuery editor will not re-scale and will appear ridiculously huge. Again, the PowerQuery editor will not rescale when the other external monitors are connected to the laptop. Closing and reopening the PowerQuery Editor will not help. The text and icons in the editor will be massive and unusable. The only way to resolve this is to (again) close Excel entirely, then reopen Excel and reopen the Power Query Editor.

So, in both cases, restarting Excel after disconnecting or reconnecting resolves the scaling issue.

I read about Office Support for High Definition Displays, then about Windows Scaling Issues for High DPI Devices. Under the workarounds on that page, it recommended to check whether applications are DPI aware (i.e. can they respond to changes in the DPI when the application is moved between displays of different resolutions (this is what happens when you connect your laptop to your monitors and your monitor is set to be your main display, so Excel pops over to the monitor automatically)).

I downloaded the recommended tool and found that Power Query is not DPI aware:



The two "Microsoft.Mashup.Containers" rows in the image above are in fact containers for the integrated Power Query add-in and as you can see from the last column, they are DPI unaware. I'm no expert, but I believe this means that Power Query has no clue that the DPI on the display being used has changed. This is described in more detail on about how to Handle High DPI And DPI Scaling in your Office Solution.

Specifically, the table about Windows DPI awareness modes and the mode DPI unaware.

In any case, since the integrated Power Query Editor (accessed via Get & Transform) is DPI unaware, you must restart Excel (and therefore the container holding Power Query) when you switch resolutions (i.e. disconnect/connect to external displays).