如何在PowerQuery公式中使用TextBox的输入？

I would like to use a PowerQuery Formula which includes input from a text box like

我想使用一个PowerQuery公式,它包含来自文本框的输入

Json.Document(Web.Contents("https://thesite.com/api/widgets",
              [Query=[name=TextBoxName.Text]]))

But I get this error

但是我得到了这个错误

Expression.Error: The import TextBoxName.Text matches no exports. Did
 you miss a module reference?

How do I use TextBoxName.Text as a parameter in my query?

如何在查询中使用TextBoxName.Text作为参数?

1 个解决方案

#1

Does it have to be a text box? Put the value in an Excel table instead and call that table "Parameters".

它必须是一个文本框吗?将值放在Excel表中,然后调用该表“Parameters”。

Then write a function that reads the parameters in the table.

然后编写一个读取表中参数的函数。

(ParameterName as text) =>
let
   ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
   ParamRow = Table.SelectRows(ParamSource, each ([Parameter] ParameterName)),
   Value=
      if Table.IsEmpty(ParamRow)=true
      then null
      else Record.Field(ParamRow{0},"Value")
in
   Value

Give that function the name fnGetParameter and load it.

将该函数命名为fnGetParameter并加载它。

Now you can use this function in another query to get the value of any parameter in the table like this:

现在,您可以在另一个查询中使用此函数来获取表中任何参数的值,如下所示:

let
   MyQuery = fnGetParameter("JSONQuery"),
   Source = Json.Document(Web.Contents("https://thesite.com/api/widgets",
              [Query=[name=MyQuery]]))

Credit goes to Ken Puls who describes this technique on his blog and also in his book "M is for (Data) Monkey".

感谢Ken Puls在他的博客上以及他的书“M is for(Data)Monkey”中描述了这种技术。

#1