通过“列表”使用“ each”关键字到函数调用 [英] 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}})

这时，出现以下错误。

getPriceMetrics查询中发生错误。 Expression.Error：我们无法应用运算符&输入文本和表格。详细信息：

运算符=&

左侧= > https://finnhub.io/api/v1/stock/metric?metric=price&token=ABCXYZ&symbol=

Right = [表]

如果有人可以提供有关如何向函数提供列表参数的见解，我将不胜感激使用每个关键字时调用。

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调用的限制，某些记录将返回为空。

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:\Users\Sikander\Google Drive\Docs\Finance\Investments\Questrade\FIRE_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:\Users\Sikander\Google Drive\Docs\Finance\Investments\Questrade\FIRE_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 值的子表。根据表的不同，您可能可以从表中选择一个元素传递给函数，从而摆脱困境。如果您将每个表的第一行 {0} 和 [Index] 列作为第一行，则结果如下： code> _ 。

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(_))

如果查询是具有列 [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.

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

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:

强大的查询：当特定值出现在另一列中时，如何在列中添加一个

编辑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)))`

这篇关于通过“列表”使用“ each”关键字到函数调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！