Excel PowerQuery中保留查询定义将表格转为普通区域的实现

问题说明 #

需将Excel中由Power Query生成的表格转换为普通单元格区域，但常规的「转换为区域」操作会永久删除工作表中的查询定义（警告提示：将表格转换为区域将删除查询定义，且无法撤销此操作）。对应的Power Query代码如下：

let
Source = Excel.CurrentWorkbook(){[Name="Table310"]}[Content],
#"Renamed Columns" = Table.RenameColumns(Source,{{"instrument_key", "Symbol1"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Symbol.1", each Symbol([Symbol1])),
#"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"symbol", "year", "W/M", "Strike", "Option", "Symbol1", "tradingsymbol", "Lot"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
#"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each Table.FromColumns(Table.ToColumns([Column1]) & Table.ToColumns([Column2]) & Table.ToColumns([Column3]))),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column2", "Column3"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type datetime}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"volume", Int64.Type}, {"oi", Int64.Type}, {"avg price", type number}, {"avgpricecprcnt", type number}, {"coi", type any}, {"Nt", Int64.Type}, {"coint", type any}, {"mf", Int64.Type}, {"Date_1", type datetime}, {"open_2", type number}, {"high_3", type number}, {"low_4", type number}, {"close_5", type number}, {"volume_6", Int64.Type}, {"oi_7", Int64.Type}, {"avg price_8", type number}, {"avgpricecprcnt_9", type number}, {"coi_10", Int64.Type}, {"Nt_11", Int64.Type}, {"coint_12", Int64.Type}, {"mf_13", type number}, {"Date_14", type datetime}, {"open_15", type number}, {"high_16", type number}, {"low_17", type number}, {"close_18", type number}, {"volume_19", Int64.Type}, {"oi_20", Int64.Type}, {"avg price_21", type number}, {"avgpricecprcnt_22", type number}, {"coi_23", Int64.Type}, {"Nt_24", Int64.Type}, {"coint_25", Int64.Type}, {"mf_26", type number}})     
in #"Changed Type"

解决方案 #

方法1：复制粘贴值（快速简便） #

• 选中Power Query生成的整个表格区域
• 按Ctrl+C复制，右键选择粘贴值（或使用快捷键Ctrl+Alt+V，在弹出窗口中选择「值」选项）
• 粘贴后得到普通单元格区域，原查询定义会保留在Power Query编辑器中（可通过「数据」选项卡→「查询管理器」查看和编辑）

方法2：在Power Query中重新加载为值 #

• 打开Power Query编辑器，选中目标查询
• 点击「主页」选项卡→「关闭并上载至」，选择「仅创建连接」后点击确定
• 打开「查询管理器」，右键点击该查询，选择「加载到」
• 在加载窗口中选择「表」，指定加载位置，同时在高级选项中选择「加载为值」（部分Excel版本可直接选择此选项）
• 生成的区域为普通单元格，原查询定义完全保留

方法3：VBA脚本批量处理（适合重复操作） #

Sub ConvertQueryTableToRangePreserveQuery()
    Dim qt As QueryTable
    Dim ws As Worksheet
    Dim targetRange As Range
    
    Set ws = ActiveSheet
    ' 定位当前工作表的第一个查询表，若有多个可修改索引或遍历
    Set qt = ws.QueryTables(1)
    Set targetRange = qt.ResultRange
    
    ' 将查询结果的值写入原区域，断开查询关联
    targetRange.Value = targetRange.Value
    qt.Delete
End Sub

• 按Alt+F11打开VBA编辑器，插入新模块并粘贴上述代码
• 运行脚本后，原区域变为普通单元格，查询定义仍保存在Power Query中

内容的提问来源于stack exchange，提问作者Sameer Wasnikar