Home
익명 23:45

Excel Power Query: "Refresh" button on Query tab does nothing for one query, but...

Excel Power Query: "Refresh" button on Query tab does nothing for one query, but other refresh methods work

I'm having a strange issue with one Power Query in one Excel workbook.

I have a workbook with a number of Power Queries. For one specific query (POReceiptHdr), the Refresh button on the Query contextual tab does nothing.

The query itself works correctly. The following methods all work: Power Query Editor->Refresh Preview, Data->Refresh, Right-click in query table->Refresh, Alt+F5, Ctrl+Alt+F5.

The only thing that fails is selecting the query output table → Query tab → Refresh. Clicking that button produces no error, no status-bar message, and no refresh activity. This is only happening for this one query. Other Power Query output tables in the same workbook refresh normally using the Query tab button, as do other queries in other workbooks.

Excel version Microsoft Excel for Microsoft 365 MSO, Version 2606 Build 16.0.20131.20154 64-bit

Query name: POReceiptHdr

It is loaded to an Excel table. The table name and Load To destination are correct. The query formula is normal and uses an ODBC source:

let
    Source = Odbc.DataSource("dsn=#####", [HierarchicalNavigation=true]),
    db_name = Source{[Name="#####",Kind="Database"]}[Data],
    dbo_Schema = db_name {[Name="dbo",Kind="Schema"]}[Data],
    ...

What I've already done:

  • I compared query settings with working queries and they looked the same.

  • I reset Excel ribbon customizations, which did nothing.

  • There are no VBA refresh handlers.

  • I examined the workbook connection — it exists and is valid.

  • ThisWorkbook.Queries("POReceiptHdr").Formula matches a duplicated query exactly.

If I duplicate the query (same M code, same source), the duplicate works correctly with Query->Refresh. So it appears to be something about the original query object, not the query logic.

What internal Excel object controls the Query tab Refresh button, and how would I repair/reset the metadata for a single Power Query without deleting/recreating the query? (This is a huge, very interconnected workbook and I don't want to duplicate the query for fear something would break.)

EDIT: Here is the xl/tables/table#.xml for the other broken query:

<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" mc:Ignorable="xr xr3" id="18" xr:uid="{4FDD1DAB-0013-44B1-B2BE-FBC29F7F7552}" name="SUPPPROD" displayName="SUPPPROD" ref="A1:P367" tableType="queryTable" totalsRowShown="0">
<autoFilter ref="A1:P367" xr:uid="{4FDD1DAB-0013-44B1-B2BE-FBC29F7F7552}"/>
<tableColumns count="16">
<tableColumn id="1" xr3:uid="{D95093F8-2631-42E0-A205-AB0C0B485E70}" uniqueName="1" name="WHS & PNUM" queryTableFieldId="141"/>
<tableColumn id="2" xr3:uid="{F7A7248A-1781-4F80-B842-6FAB74288B82}" uniqueName="2" name="Receipt Date.SPID" queryTableFieldId="142"/>
<tableColumn id="3" xr3:uid="{87A3FD4F-2A40-4EE4-BCE9-00790704CF5D}" uniqueName="3" name="Receipt Date.PNUM" queryTableFieldId="143"/>
<tableColumn id="4" xr3:uid="{8DC7450A-B95B-45EB-912E-E36E7B82D9E9}" uniqueName="4" name="Receipt Date.Date Last Ordered" queryTableFieldId="144"/>
<tableColumn id="14" xr3:uid="{B3CDF185-6342-4486-9C4D-89CAC5D324C9}" uniqueName="14" name="Receipt Date.RMC_B1" queryTableFieldId="154"/>
<tableColumn id="15" xr3:uid="{888B26E7-FD61-4C1C-AD70-77AB635DB835}" uniqueName="15" name="Receipt Date.UseSchedule" queryTableFieldId="155"/>
<tableColumn id="5" xr3:uid="{35AE7112-7537-4C77-926D-42B0B282079E}" uniqueName="5" name="Receipt Date.EffectiveDate" queryTableFieldId="145"/>
<tableColumn id="6" xr3:uid="{776E9199-38DB-4F86-A508-56C7BD1474A0}" uniqueName="6" name="Receipt Date.SUPPNAME.SNAME1" queryTableFieldId="146"/>
<tableColumn id="7" xr3:uid="{D63B7AF8-34C2-4CE7-974D-E074B70FA0AF}" uniqueName="7" name="Receipt Date.PRODFIL2.VNUM" queryTableFieldId="147"/>
<tableColumn id="8" xr3:uid="{D38D5EDA-DE4A-46E5-8E7C-8FA9BD404B26}" uniqueName="8" name="Receipt Date.PRODFIL2.SecondDefaultSupplier_cst" queryTableFieldId="148"/>
<tableColumn id="9" xr3:uid="{7152A8F5-D550-4910-87E9-D5DECFCDDDF6}" uniqueName="9" name="Receipt Date.SUPPNAME.SNAME1.1" queryTableFieldId="149"/>
<tableColumn id="10" xr3:uid="{0BE967EE-2918-42A4-82AD-C3BD25179EB8}" uniqueName="10" name="Receipt Date.Default Supplier Cost" queryTableFieldId="150"/>
<tableColumn id="11" xr3:uid="{E147044F-8CB0-40D9-919E-DF04344CC93C}" uniqueName="11" name="Receipt Date.POReceiptHdr (2).ReceiptDate" queryTableFieldId="151"/>
<tableColumn id="12" xr3:uid="{2BA56A28-210F-4231-A92A-02941AA7FFD8}" uniqueName="12" name="Receipt Date.POReceiptHdr (2).POHDR (2).WHS" queryTableFieldId="152"/>
<tableColumn id="13" xr3:uid="{07FF1A81-145F-452C-BC29-A1B9A0282E22}" uniqueName="13" name="Receipt Date.WHS & PNUM" queryTableFieldId="153"/>
<tableColumn id="16" xr3:uid="{28769DC6-146D-4E77-B299-E308B688572A}" uniqueName="16" name="Cost or Scheduled Cost" queryTableFieldId="156"/>
</tableColumns>
<tableStyleInfo name="TableStyleMedium2" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>

and the equivalent working query:

<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" mc:Ignorable="xr xr3" id="23" xr:uid="{B54EAE16-5F30-42A4-B54C-0AFB49B8FF06}" name="SUPPPROD__2" displayName="SUPPPROD__2" ref="A1:P367" tableType="queryTable" totalsRowShown="0">
<autoFilter ref="A1:P367" xr:uid="{B54EAE16-5F30-42A4-B54C-0AFB49B8FF06}"/>
<tableColumns count="16">
<tableColumn id="1" xr3:uid="{76EE7CF9-832B-43F0-8585-2F48BF79B625}" uniqueName="1" name="WHS & PNUM" queryTableFieldId="1"/>
<tableColumn id="2" xr3:uid="{4612D5B5-09CB-4699-BEE6-6A1364656DEF}" uniqueName="2" name="Receipt Date.SPID" queryTableFieldId="2"/>
<tableColumn id="3" xr3:uid="{641BDFEC-1104-4FD0-BFB7-4E58075C39BF}" uniqueName="3" name="Receipt Date.PNUM" queryTableFieldId="3"/>
<tableColumn id="4" xr3:uid="{FCA88AD0-5F49-4D35-B92E-7BF22E9C783A}" uniqueName="4" name="Receipt Date.Date Last Ordered" queryTableFieldId="4"/>
<tableColumn id="5" xr3:uid="{F130F3CF-7E50-417C-84DD-F387B6ECD7F0}" uniqueName="5" name="Receipt Date.RMC_B1" queryTableFieldId="5"/>
<tableColumn id="6" xr3:uid="{4CB6C470-0DE3-4830-817D-3E82C41F5CBA}" uniqueName="6" name="Receipt Date.UseSchedule" queryTableFieldId="6"/>
<tableColumn id="7" xr3:uid="{B5152D32-8B13-476F-B2C0-30F2214FB29A}" uniqueName="7" name="Receipt Date.EffectiveDate" queryTableFieldId="7"/>
<tableColumn id="8" xr3:uid="{A4531446-E956-4DBC-85E4-1524C067C80B}" uniqueName="8" name="Receipt Date.SUPPNAME.SNAME1" queryTableFieldId="8"/>
<tableColumn id="9" xr3:uid="{EAA28CD6-E575-47DD-A6C7-434CC8BD2FB8}" uniqueName="9" name="Receipt Date.PRODFIL2.VNUM" queryTableFieldId="9"/>
<tableColumn id="10" xr3:uid="{CE86036D-36C4-4A93-BF18-D89B4ADD458C}" uniqueName="10" name="Receipt Date.PRODFIL2.SecondDefaultSupplier_cst" queryTableFieldId="10"/>
<tableColumn id="11" xr3:uid="{DBB98E06-F6D3-4285-9E5C-238D4C39ABB9}" uniqueName="11" name="Receipt Date.SUPPNAME.SNAME1.1" queryTableFieldId="11"/>
<tableColumn id="12" xr3:uid="{58C624BA-C91C-49A6-A867-1ABCBEF15F09}" uniqueName="12" name="Receipt Date.Default Supplier Cost" queryTableFieldId="12"/>
<tableColumn id="13" xr3:uid="{53EB5E07-582F-41B8-AD23-946AB80F59BB}" uniqueName="13" name="Receipt Date.POReceiptHdr (2).ReceiptDate" queryTableFieldId="13"/>
<tableColumn id="14" xr3:uid="{F35B9264-453E-43A4-A86E-B9BABEBEA2FB}" uniqueName="14" name="Receipt Date.POReceiptHdr (2).POHDR (2).WHS" queryTableFieldId="14"/>
<tableColumn id="15" xr3:uid="{3AA44569-28ED-4FC3-9132-6CB709123ACB}" uniqueName="15" name="Receipt Date.WHS & PNUM" queryTableFieldId="15"/>
<tableColumn id="16" xr3:uid="{7AC6CDB9-57A6-49C5-A8AA-CCD7DE87E399}" uniqueName="16" name="Cost or Scheduled Cost" queryTableFieldId="16"/>
</tableColumns>
<tableStyleInfo name="TableStyleMedium7" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>

I noticed the queryTableFieldIds for the non-Query->Refresh-ing query are way up in the 140s-150s but the queryTableFieldIds for the working query are sequential starting at 1. Not sure if that has anything to do with the problem.


Top Answer/Comment:

The Issue

The workbook appears to have inconsistent Open XML metadata, not a problem with the query, connection, or data source itself.

Troubleshooting Method

Since an .xlsx file is an Open XML package (a ZIP archive of XML parts), make a copy of the workbook, rename it to .zip, extract it, and compare its contents with a working workbook (or with a copy where the affected query has been recreated).

Microsoft topics for working with spreadsheet documents using the Open XML SDK for Office:

The parts most relevant to Power Query tables are:

  • xl/connections.xml
  • xl/queryTables/
  • xl/tables/
  • Relationship files under xl/_rels/

Op Update

An OP comparison of the broken and rebuilt workbooks revealed the key difference:

  • The working/rebuilt workbook contains xl/queryTables/queryTable##.xml entries whose connectionId matches the affected queries.
  • The original workbook has no corresponding queryTable##.xml parts for the two queries that ignore the Query -> Refresh command.

Indication of Issue

This suggests the workbook's internal metadata for those query tables is missing or corrupted. It also explains why other refresh methods can still work even though the Query tab's Refresh button does nothing.

The issue is with the workbook's internal XML metadata. Recreating the affected query/table causes Excel to regenerate the missing queryTable metadata, which is consistent with the rebuilt workbook behaving normally.

Comparing the Open XML package is an possible way to identify these kinds of metadata inconsistencies and determine exactly which XML parts differ between the working and non-working workbook.

No Documented Manual Metadata Editing Fix

I couldn't find a supported way to rebuild the missing queryTable metadata without recreating the query. Manual Open XML editing may be possible, but it is undocumented and should only be attempted with backups and caution.

상단 광고의 [X] 버튼을 누르면 내용이 보입니다