Power Query 是 Excel 和 Power BI 中强大的数据获取、转换和加载工具

Power Query 链接是什么

Power Query 是 Excel 和 Power BI 中强大的数据获取、转换和加载工具。Power Query 链接指的是在 Excel 或 Power BI 里通过 Power Query 建立的与外部数据源的连接。这些数据源可以是各种类型，像文本文件（CSV、TXT）、数据库（SQL Server、Access）、网页、Excel 文件等。借助 Power Query 链接，你能从数据源获取数据，对其进行清洗、转换，最终加载到工作簿或报表中。

手工插入 Power Query 链接的步骤

在 Excel 中

1. 获取数据：点击 “数据” 选项卡，在 “获取外部数据” 组中选择合适的数据源类型，比如 “自文本 / CSV”“自数据库” 等。
2. 选择数据源：在弹出的文件选择对话框或者数据库连接对话框里，选择要连接的数据源。
3. 导航数据：Power Query 编辑器会显示数据源中的数据结构，你可以选择要加载的数据表或数据区域。
4. 转换数据：在 Power Query 编辑器中对数据进行清洗、转换操作，例如筛选、排序、合并列等。
5. 加载数据：完成数据转换后，点击 “关闭并上载” 按钮，将处理后的数据加载到 Excel 工作表中。

使用 VBA 代码插入 Power Query 链接

以下是一个示例代码，展示如何使用 VBA 在 Excel 中通过 Power Query 连接到 CSV 文件并加载数据：VBA

Sub InsertPowerQueryLink()Dim wb As WorkbookDim ws As WorksheetDim qt As WorkbookQueryDim sourceFile As StringDim queryName As String' 设置当前工作簿Set wb = ThisWorkbook' 设置要插入数据的工作表Set ws = wb.Sheets("Sheet1") ' 可根据实际情况修改工作表名称' 设置数据源文件路径sourceFile = "C:\YourFilePath\YourFile.csv" ' 替换为实际的 CSV 文件路径' 设置查询名称queryName = "MyPowerQuery"' 创建新的 Power Query 查询Set qt = wb.Queries.Add( _Name:=queryName, _Formula:="let" & Chr(13) & Chr(10) & _"    Source = Csv.Document(File.Contents(""" & sourceFile & """),[Delimiter="","", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & Chr(10) & _"    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])" & Chr(13) & Chr(10) & _"in" & Chr(13) & Chr(10) & _"    #""Promoted Headers""" _)' 将查询结果加载到工作表中ws.ListObjects.Add(SourceType:=0, Source:=qt, Destination:=ws.Range("A1")).Name = "MyTable"
End Sub

代码解释

1. 设置工作簿和工作表：
   • Set wb = ThisWorkbook：设置当前工作簿。
   • Set ws = wb.Sheets("Sheet1")：设置要插入数据的工作表，你可以根据实际情况修改工作表名称。
2. 设置数据源文件路径和查询名称：
   • sourceFile = "C:\YourFilePath\YourFile.csv"：你要把这个路径替换成实际的 CSV 文件路径。
   • queryName = "MyPowerQuery"：设置查询的名称。
3. 创建新的 Power Query 查询：
   • wb.Queries.Add：创建一个新的 Power Query 查询。
   • Formula：定义 Power Query 的 M 语言公式，这里是连接到 CSV 文件并提升标题行。
4. 将查询结果加载到工作表中：
   • ws.ListObjects.Add：把查询结果加载到指定的工作表区域（这里是 A1 单元格）。

使用方法

1. 打开 Excel 文件，按下 Alt + F11 打开 VBA 编辑器。
2. 在 VBA 编辑器中，插入一个新模块（“插入” -> “模块”）。
3. 把上述代码复制到新模块中。
4. 将 sourceFile 变量的值替换成实际的 CSV 文件路径。
5. 按下 F5 运行代码。

运行代码后，Excel 会通过 Power Query 连接到指定的 CSV 文件，并将数据加载到工作表中。