传递一个“列表"；使用“每个"关键字进行函数调用 [英] Passing a "list" to a function call with the 'each' keyword

问题描述

首先，我承认我不是 M 或 Power Query 专家，尽管我确实对 Power BI 有一些经验.

First things first, I will confess I am not an M or Power Query expert, though I do have some experience with Power BI.

我正在尝试开发一个股票投资组合，该投资组合将跟踪自定义的股票列表及其价格历史记录和其他指标.由于我要解决的问题，我的部分代码基于以下博客:

I am trying to develop a stock portfolio that will track a customised list of stocks with their price history and other metrics. Part of my code is based on the following blog, due to the problem I am trying to solve:

https://datachant.com/2016/08/09/sentiment-analysis-power-bi-part-2/

我的项目中有以下函数 getPriceMetrics，它将采用一个参数 Sym 并返回单个股票代码的价格指标:

I have the following function getPriceMetrics in my project that will take one parameter Sym and return the price metrics for an individual stock symbol:

(Sym as any) => let
    Source = Json.Document(Web.Contents("
        https://finnhub.io/api/v1/stock/metric?metric=price&token=ABCXYZ&symbol=" & Sym))
in
    Source

Sym 参数基于包含股票代码列表的查询".

The parameter Sym is based on a "Query" that contains a list of stock symbols.

当我尝试用一​​个单独的符号调用它时，该函数本身运行良好.但我想针对股票代码列表运行该函数 - 因此上面的博客.

The function itself works well when I try to call it with one individual symbol. But I would like to run the function against a list of stock symbols - hence the blog above.

我认为博客中提供的解决方案可以很好地满足我的目的.唯一的问题是我无法使用 each 关键字正确调用函数，我认为:

I think the solution offered in the blog would serve my purpose well. The only hitch is that I am unable to make the function call correctly with the each keyword, I think:

Table.Group(#"Renamed Columns", {"Index"},
    {{"Data", each getPriceMetrics(_), type record}})

此时，我收到以下错误.

At this point, I get the following error.

getPriceMetrics"查询中出现错误.Expression.Error:我们不能应用运算符 &输入文本和表格.详情:
运算符 = &
左 = https://finnhub.io/api/v1/stock/metric?metric=price&token=ABCXYZ&symbol=
右 = [表格]

如果有人能提供有关在使用 each 关键字时如何为函数调用提供列表参数的任何见解，我将不胜感激.

I would appreciate it if someone can offer any insight in how to supply a list argument to the function call when using the each keyword.

这是我的原始方法，从技术上讲是可行的，但由于每秒 30 个 API 调用的限制，一些记录返回为 null.

Here's my original approach, which is working technically speaking, but due to the limit of 30 API calls per second, some records are returned as null.

let
Source = Excel.Workbook(File.Contents("C:UsersSikanderGoogle DriveDocsFinanceInvestmentsQuestradeFIRE_strategy.xlsx"), null, true),
Portfolio_Sheet = Source{[Item="Portfolio",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Portfolio_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Symbol", type text}, {"Company", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "getPriceMetrics", each getPriceMetrics([Symbol])) 
in #"Invoked Custom Function"

为了解决这个问题，我遵循了博客中建议的方法，该方法允许人们欺骗"这个 API 限制，可以这么说，通过索引和分组，如下所示:

To counter this, I followed the approach suggested in the blog that will allow one to "cheat" this API limit, so to speak, by indexing and grouping, like so:

let
Source = Excel.Workbook(File.Contents("C:UsersSikanderGoogle DriveDocsFinanceInvestmentsQuestradeFIRE_strategy.xlsx"), null, true),
Portfolio_Sheet = Source{[Item="Portfolio",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Portfolio_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Symbol", type text}, {"Company", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 10), Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Integer-Divided Column",{{"Symbol", "Ticker"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Index"}, {{"Data", each getPriceMetrics(_), type record}})

在#分组行"中

推荐答案

发生错误是因为您将表而不是固定符号传递给函数.该表是特定(单个)Index 值的 #"Renamed Columns" 的子表.根据您的表是什么，您可能能够从该表中选择一个元素来传递给函数.如果您获取每个表 _ 的第一行 {0} 和 [Index] 列，情况如下所示.

The error is happening because you are passing a table to your function instead of a fixed symbol. That table is the subtable of #"Renamed Columns" for a particular (single) Index value. Depending on what your table is, you might be able to get away with picking a single element from that table to pass to the function. Here's what that would look like if you took the first row {0} and [Index] column of each table _.

Table.Group(#"Renamed Columns", {"Index"},
    {{"Data", each getPriceMetrics(_{0}[Index]), type record}})

但是，我认为完全没有理由使用 Table.Group.如果您的查询"是一个列表，那么您只需编写以下内容即可将函数应用于每个元素:

However, I don't think there's a good reason to be doing Table.Group at all. If your "Query" is a list, then you can simply write the following to apply the function to each element:

List.Transform(Query, each getPriceMetrics(_))

如果Query"是具有 [Sym] 列的表，您可以将上面的 Query 替换为 Query[Sym]，因为表格列是一个列表.

If "Query" is a table with a column [Sym] you can replace Query with Query[Sym] in the above since a table column is a list.

或者，您可以使用简单的公式 getPriceMetrics([Sym]) 在查询编辑器 GUI 中添加自定义列，它将使用以下代码创建一个步骤:

Alternatively, you could add a customized column in the query editor GUI with the simple formula getPriceMetrics([Sym]) and it will create a step with the following code:

Table.AddColumn(#"Previous Step Name Here", "Custom", each getPriceMetrics([Sym]))

<小时>

我最近的这个答案有一些在查询编辑器中使用与此处相关的函数的屏幕截图:

---

This recent answer of mine has some screenshots of working with functions in the query editor that are relevant here as well:

Power Query:当特定值出现在另一列中时如何将一个添加到列中

编辑 2: 正如下面的评论所指出的，这个特定问题的解决方案与使语法完全正确无关，而是需要睡眠功能，正如我在此 链接的帖子.

Edit 2: As pointed out in the comments below, the solution to this particular problem does not have to do with getting the syntax exactly correct but require a sleep functionality as I pointed to in this linked post.

Table.AddColumn(#"Changed Type", "PriceMetrics",
    each Function.InvokeAfter(()=>getPriceMetrics([Symbol]),#duration(0,0,0,1)))`

这篇关于传递一个“列表"；使用“每个"关键字进行函数调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！