动态获取相对路径

尽管PQ非常灵活,但仍然存在一些不太方便的地方,比如文件路径为绝对路径,无法动态刷新,做好的报告发给同事后必须修改路径才能刷新。但是PQ做不到的,工作表函数也许能做到。

首先我们打开报告,新建一个sheet,先来测试一下,在任一单元格输入=CELL("filename"),返回了当前文件的动态路径,例如C:\Users\Admin\Desktop\[test.xlsx]Sheet1,如果我们把文件换个路径存放或者发给同事,返回的结果也会跟着变化,我们要的正是这个效果。

然后开始构建路径表,新建两列,一列手动输入源文件的文件名,另一列输入公式=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&A2&".xlsx",返回C:\Users\Admin\Desktop\表1.xlsx。公式中&右边的部分根据实际情况自行修改,就是用left+find截取cell返回的左边部分,然后&自己的文件路径,很简单我就不详细介绍了,公式输完看下结果是否为正确的路径。

路径表做好后,将其导入PQ,创建一个新的查询为"路径表",当然名字你可以随便起,那么其他表的路径只需要深化出"路径表"中对应的路径,即为相对路径。比如常规导入excel文件生成的公式为= Excel.Workbook(File.Contents("C:\Users\Admin\Desktop\表3.xlsx"), null, true)
那么我们只需要把绝对路径替换为"路径表"中对应的相对路径即可,"表3"的路径在"路径表"中[路径]字段下的第3行,也就是路径表[路径]{2},替换得到= Excel.Workbook(File.Contents(路径表[路径]{2}), null, true)

看下返回的结果是不是和替换前一摸一样?至此动态获取相对路径完成。

需要注意的是,创建路径表有两种方式:
1、路径表随报告文件
即路径表和报告在同一个文件中,这样可以通过数据-从表格将路径表导入,优点是能实现真正的全动态相对路径,转移路径无需修改任何参数。缺点就是只能用于Power Query for Excel。
2、路径表和报告分离
即两者不在同一个文件中,是通过新建查询-从文件-从工作簿导入的,优点是可用于Power BI Desktop。缺点是无法动态获取路径表本身的路径,转移路径需要更改路径表的路径,不过也仅需要改这一个路径,其他源文件的路径即可通过路径表获取,比起改全部路径还是方便很多。
请根据自身情况选择。

如出现以下错误,点击文件-文件和设置-查询选项,把隐私设置为始终忽略即可。

30 Replies to “动态获取相对路径”

  1. Formula.Firewall: 查询“Sheet1”(步骤“源”) 将引用其他查询或步骤,因此可能不会直接访问数据源。请重新生成此数据组合。我的提示这个?

  2. 你好, 这个对我很有帮助。想具体问下,替换绝对路径到相对路径的具体操作是怎样的? 简单吧查询的名字copy paste 进去,还是要通过其他操作实现?我直接输入查询表的名称,在query editor 中不能够识别出来。

  3. 微软BI Community上有人3年前就提出建议增加动态路径的功能,然鹅,到现在都没有加入这个很实用的功能,而且实现起来感觉并不复杂...还是感谢

  4. 今天刷新了个动态路径的表,半个小时没反应,然后我把路径粘贴为值以后,两分钟完事,动态路径是个坑。打开新的文件以后路径还会跟着变。。。这也很蛋疼

  5. 操作方法一下,一直报这个 错,想不出问题出在哪。。。
    Expression.Error: 枚举中没有足够的元素来完成该操作。

    1. 路径表是一张表,路径表[路径]是取表中的一列,得到list,路径表[路径]{2}取list中的第三个。
      你的报错的意思是指,list中总共两行,你去第三个,没有足够的元素。
      所以先在编辑栏输入=路径表[路径]看下list一共有几个元素,再看是否越界了。

  6. 路径表[路径]是取表中的一列,得到list,路径表[路径]{2}取list中的第三个。
    那请问list中第一个怎么写?

  7. 我是Ctrl+F3把cell写好的函数地址定义的名称,然后Excel.CurrentWorkbook()获取筛选Name为定义名称的content下的table,然后再深化得出字段,放在本地的话都没问题,但是onedrive的话路径就不行了

    1. 数据源和PQ处理后的结果置于同一工作簿的不同sheet下,则自动相对引用,不需要单独设置路径。

  8. 您好,使用一下该步骤,一个成功了 ,另一个按照相同步骤,一直报错Formula.Firewall: Query 'CustructionPriceModel' (step 'Removed Duplicates') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination., 请问是什么问题呀 ?

  9. Expression.Error: 无法对类型 Text 应用字段访问。
    详细信息:
    Value=C:\Users\Administrator\Desktop\ok\【苏宁超市】产品表(1).xlsx
    Key=路径

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注