如何获取Power Query所有首步骤中的全部SQL查询列表？

方法1：用M代码遍历查询提取SQL #

在Power BI或Excel中，可通过编写M代码批量提取所有查询首步骤的SQL语句：

1. 新建空白查询，打开高级编辑器，替换为以下代码：

let
    // 获取当前工作簿所有查询
    AllQueries = #sections[Section1],
    // 转换为表格格式
    QueriesTable = Record.ToTable(AllQueries),
    // 过滤掉函数类型的查询
    FilteredQueries = Table.SelectRows(QueriesTable, each not Value.Is(Value.From(_[Value]), type function)),
    // 添加列提取首步骤SQL
    AddSQLColumn = Table.AddColumn(FilteredQueries, "首步骤SQL", each 
        let
            QuerySteps = Value.From(_[Value])[Steps],
            FirstStep = List.First(QuerySteps),
            StepValue = FirstStep[Value]
        in
            // 处理带NativeQuery的SQL源
            if Type.Is(Value.Type(StepValue), type table) and Record.HasFields(StepValue, {"NativeQuery"}) then
                StepValue[NativeQuery]
            // 处理直接用Sql.Database的场景
            else if Text.Contains(Text.From(StepValue), "Sql.Database") then
                let
                    StepText = Text.From(StepValue),
                    QuotePositions = Text.PositionOf(StepText, """", Occurrence.All)
                in
                    if List.Count(QuotePositions) >=4 then
                        Text.Range(StepText, QuotePositions{2}+1, QuotePositions{3}-QuotePositions{2}-1)
                    else "无SQL语句"
            else "无SQL语句"
    ),
    // 整理结果列
    FinalTable = Table.SelectColumns(AddSQLColumn, {"Name", "首步骤SQL"})
in
    FinalTable

2. 运行查询后，会得到包含查询名称和对应首步骤SQL的表格，标记“无SQL语句”的行表示该查询首步骤不基于SQL。

方法2：Excel中用VBA脚本提取 #

如果是在Excel环境下，可通过VBA批量遍历Power Query查询：

1. 按Alt+F11打开VBA编辑器，插入模块，粘贴以下代码：

Sub ExtractPowerQuerySQL()
    Dim qry As WorkbookQuery
    Dim targetStep As WorkbookQueryStep
    Dim sqlContent As String
    Dim resultSheet As Worksheet
    Dim rowIndex As Integer
    
    ' 创建结果工作表
    Set resultSheet = ThisWorkbook.Sheets.Add
    resultSheet.Name = "SQL查询列表"
    resultSheet.Range("A1").Value = "查询名称"
    resultSheet.Range("B1").Value = "首步骤SQL"
    rowIndex = 2
    
    For Each qry In ThisWorkbook.Queries
        If qry.Steps.Count > 0 Then
            Set targetStep = qry.Steps(1)
            ' 解析Sql.Database场景的SQL
            If InStr(targetStep.Formula, "Sql.Database") > 0 Then
                Dim formulaSplit As Variant
                formulaSplit = Split(targetStep.Formula, """")
                If UBound(formulaSplit) >= 3 Then
                    sqlContent = formulaSplit(3)
                Else
                    sqlContent = "无法解析SQL"
                End If
            ' 解析NativeQuery场景的SQL
            ElseIf InStr(targetStep.Formula, "NativeQuery") > 0 Then
                Dim startPos As Integer, endPos As Integer
                startPos = InStr(targetStep.Formula, "NativeQuery(") + 12
                endPos = InStr(startPos, targetStep.Formula, ",") - 1
                sqlContent = Mid(targetStep.Formula, startPos, endPos - startPos + 1)
                sqlContent = Replace(sqlContent, """", "")
            Else
                sqlContent = "无SQL语句"
            End If
            
            ' 写入结果表
            resultSheet.Range("A" & rowIndex).Value = qry.Name
            resultSheet.Range("B" & rowIndex).Value = sqlContent
            rowIndex = rowIndex + 1
        End If
    Next qry
    
    ' 自动调整列宽
    resultSheet.Columns("A:B").AutoFit
    MsgBox "提取完成，结果已保存到工作表：" & resultSheet.Name
End Sub

2. 运行宏后，会自动生成新工作表，列出所有查询的首步骤SQL内容。

注意事项 #

• 以上方法适配常见的Sql.Database和NativeQuery场景，若使用自定义SQL连接方式，需调整代码中的解析逻辑。
• 隐藏或加密的查询需先解除限制才能正常提取。

内容的提问来源于stack exchange，提问作者Przemyslaw Remin