如何在PowerQuery公式中使用来自文本框的输入? [英] How can I use input from a TextBox in a PowerQuery Formula?

问题描述

我想使用PowerQuery公式，其中包括来自类似文本框的输入

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

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

但是我得到这个错误

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

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

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

推荐答案

是否必须是文本框?将该值放在Excel表中，然后将该表称为参数".

Does it have to be a text box? Put the value in an Excel table instead and call that table "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

赋予该函数名称fnGetParameter并加载它.

Give that function the name fnGetParameter and load it.

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

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]]))

信用归功于Ken Puls，他在他的博客，以及他的书"M是用于(数据)猴子".

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

这篇关于如何在PowerQuery公式中使用来自文本框的输入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！