I use PowerQuery quite a bit together with Excel (Microsoft 365). Excel has started crashing whenever I access Table Design / Properties for a table created by PowerQuery. This is the scenario: Put cursor in the table Click Table Design menu click "Properties" (which is present in the "External Data Table" section of the ribbon). [Note: Properties will be greyed out for an ordinary table - AFAIK, it's only accessible for a Table created by PQ.] make some change in the window that pops up In all existing workbooks, and even a brand new one I create , following Step 3 (or sometimes Step 4) Excel crashes - and it usually restarts. So, the properties function under Table Design is inaccessible! What could be causing this? How to fix it?

I can confirm this works. Selecting properties in the 'data tab' can crash it. Changing name / database / changing SQL statement or just pointing to either can crash the application. Using the 'queries and connection' route, doesn't crash the application and it works like it always has. It even has the SAME menus! Feels like it's something that Microsoft doesn't want you to use anymore and it's forcing you to use the more annoying and time-consuming alternative PowerQuery. PowerQuery is something I use for manipulating tables, not for simple selects. The 'previous' SQL function isn't even available anymore via button in the ribbon. You can only start this by adding a hidden button to the quick-start-ribbon. As soon as I uses something from the 'previous' SQL-function. My Excel application crashes. So it's definitely a bug or even intended, so users are forced to use the 'current' SQL function.

Fantastic okshef! Thanks - I didn't have time to look into that bit. Very useful. Just hope that Microsoft take note and get the bug fixed soon.

Thanks okshef 👍😊 It does indeed work: testing was a pain as just looking at the properties crashes the spreadsheet. Having this workaround is a lifesaver ... until the Excel team at Microsoft sort the bug out. I tried and failed - with my VBA (lack of) knowledge the bit that I was missing was the right object Selection.ListObject Thanks again

I have the same scenario as Tristan: Microsoft Query pulling from SQL dB. However, the property that I am trying to change is "preserve column sort/filter/layout" which is not available when accessed via Data-Queries&Connections unfortunately. Even cancelling out of the Data-Properties box causes a crash. Very frustrating For me, this type of operation was common and no problem. Yes, I agree Microsoft has introduced a bug - it would be great if they would fix it. Has anyone tried changing the query properties through VBA?

Excel crashes in Table Properties

Anonymous

I use PowerQuery quite a bit together with Excel (Microsoft 365).

Excel has started crashing whenever I access Table Design / Properties for a table created by PowerQuery.

This is the scenario:

- Put cursor in the table
- Click Table Design menu
- click "Properties" (which is present in the "External Data Table" section of the ribbon). [Note: Properties will be greyed out for an ordinary table - AFAIK, it's only accessible for a Table created by PQ.]
- make some change in the window that pops up

In all existing workbooks, and even a brand new one I create, following Step 3 (or sometimes Step 4) Excel crashes - and it usually restarts. So, the properties function under Table Design is inaccessible!

What could be causing this? How to fix it?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

52 answers

Anonymous

2025-01-31T10:24:28+00:00
I can confirm this works.
- Selecting properties in the 'data tab' can crash it.
- Changing name / database / changing SQL statement or just pointing to either can crash the application.
Using the 'queries and connection' route, doesn't crash the application and it works like it always has. It even has the SAME menus!

Feels like it's something that Microsoft doesn't want you to use anymore and it's forcing you to use the more annoying and time-consuming alternative PowerQuery.

PowerQuery is something I use for manipulating tables, not for simple selects.

The 'previous' SQL function isn't even available anymore via button in the ribbon.

You can only start this by adding a hidden button to the quick-start-ribbon.

As soon as I uses something from the 'previous' SQL-function. My Excel application crashes.

So it's definitely a bug or even intended, so users are forced to use the 'current' SQL function.
Was this answer helpful?

2 people found this answer helpful.

0 comments No comments
Anonymous

2025-01-29T21:02:03+00:00

Fantastic okshef! Thanks - I didn't have time to look into that bit. Very useful. Just hope that Microsoft take note and get the bug fixed soon.
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2025-01-29T18:15:58+00:00

Thanks okshef 👍😊

It does indeed work: testing was a pain as just looking at the properties crashes the spreadsheet.

Having this workaround is a lifesaver ... until the Excel team at Microsoft sort the bug out.

I tried and failed - with my VBA (lack of) knowledge the bit that I was missing was the right object

Selection.ListObject

Thanks again
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments

Anonymous

Has anyone tried changing the query properties through VBA?

Yes, it works. Set your parameters

Sub QueryTable_Properties()

*With Selection.ListObject.QueryTable* 

    *.RowNumbers = False* 

    *.FillAdjacentFormulas = False* 

    *.PreserveFormatting = True* 

    *.RefreshOnFileOpen = False* 

    *.BackgroundQuery = True* 

    *.RefreshStyle = xlInsertDeleteCells* 

    *.SavePassword = False* 

    *.SaveData = True* 

    *.AdjustColumnWidth = True* 

    *.RefreshPeriod = 0* 

    *.PreserveColumnInfo = True* 

*End With*

End Sub

0 comments

Anonymous

2025-01-27T14:29:43+00:00

I have the same scenario as Tristan: Microsoft Query pulling from SQL dB.

However, the property that I am trying to change is "preserve column sort/filter/layout" which is not available when accessed via Data-Queries&Connections unfortunately.

Even cancelling out of the Data-Properties box causes a crash. Very frustrating

For me, this type of operation was common and no problem. Yes, I agree Microsoft has introduced a bug - it would be great if they would fix it.

Has anyone tried changing the query properties through VBA?
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments