如何在Power查询中引用单元格的值

I'm having multiple PowerQuery queries that I would like to feed the value of a cell in my Excel file. In this particular case, the full path to the sourcefile name.

我有多个PowerQuery查询，我想在我的Excel文件中提供单元格的值。在本例中，源文件名的完整路径。

Is there any way I can get this into PowerQuery?

有什么办法可以把它放到PowerQuery上吗?

1 个解决方案

#1

This can be achieved using a named range and a custom function in PowerQuery:

这可以在PowerQuery中使用一个命名范围和一个自定义函数实现:

Name the cell you need to refer (type in a name into the file left of the formula bar) - e.g. SourceFile
命名需要引用的单元格(将名称输入到公式栏的文件中)-例如SourceFile。
Insert a new blank PowerQuery query (PowerQuery ribbon -> From other sources)
插入一个新的空白PowerQuery查询(PowerQuery ribbon ->来自其他来源)
In the PowerQuery editor, go to View -> Advanced Editor and paste the following code;
在PowerQuery editor中，转到View ->高级编辑器并粘贴以下代码;

(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

Name the query to GetValue (Name property in the Query settings pane on the right)
将查询命名为GetValue(右侧查询设置窗格中的Name属性)

Now you can access the any named cell in you queries, using GetValue(cellName) - e.g.

现在，您可以使用GetValue(cellName)来访问查询中的任何命名单元格。

= Excel.Workbook(File.Contents(GetValue("SourceFile")))

#1