07 制作Power Query动态数据源

之前给大家分享了如何使用M函数汇总指定工作簿或文件夹的数据，随之产生了一个新问题：

由于PQ读取的工作簿路径是固定的；当工作簿从一台电脑发送到另一台电脑，或者工作簿所在文件夹的位置发生了改变，PQ无法自动更新正确的工作簿路径，导致程序错误。

今天就来给大家分享一下如何避免这个错误：制作动态文件路径。

以汇总当前工作簿多工作表数据为例，操作步骤如下▼

步骤1：

在当前工作簿新建一张工作表，命名为'路径'。

在A1单元格输入:文件路径。

在A2单元格输入以下函数公式▼

代码看不全可以左右拖动..▼

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

函数的作用是动态返回公式所在工作簿的完整路径，模拟结果如下图所示

步骤2：

选中A1单元格，按<Ctrl+T>组合键，打开[创建表]对话框，勾选【表包含标题】复选框，单击【确定】按钮，将当前数据区域转换为超级表。

选中A2单元格，在[表设计]选项卡下的在'表名称'框可以查看当前超级表的名称，例如本例为'表1'——记住它……

步骤3：

采用手工操作或编写M函数的方式，汇总当前工作簿工作表的数据成一张总表。之后在PQ的视图选项下打开高级编辑器，此时可见代码类似如下图所示。

很明显，File.Contents的参数是一个常数，我们需要将它替换为'路径'工作表A2单元格由函数返回的动态路径。

在步骤源前新增一个步骤，输入代码如下▼

动态路径 = Excel.CurrentWorkbook(){[Name="表1"]}[Content][文件路径]{0}

这句代码中的函数和含义我们上一章详细讲解过了，这里不再重复。

不过需要说明的是，代码中的"表1"并非固定不变的，这应以你的路径表的数据转换为超级表后生成的实际表名为准（见上图）。

此时我们只需要将File.Contents的参数替换为步骤名称动态路径，即可完成动态获取当前工作簿完整路径的任务。如果文件移动了位置，打开工作簿后刷新一下即可。

就是这么简单。

……

小贴士：

这是一个简单的案例，功能是汇总当前工作簿工作表的数据；如果你所操作的是同文件夹下的其它指定工作簿，可以将上文中路径表A2单元格的函数修改为：

=SUBSTITUTE(CELL("filename",A1),TRIM(RIGHT(SUBSTITUTE(CELL("filename",A1),"\",REPT(" ",100)),99)),"") & "你的文件名"

公式中"你的文件名"指的是当前文件夹下指定文件带后缀的名字，例如，假设文件名是看见星光.xlsx，则公式如下：

=SUBSTITUTE(CELL("filename",A1),TRIM(RIGHT(SUBSTITUTE(CELL("filename",A1),"\",REPT(" ",100)),99)),"") & "看见星光.xlsx"

……