excel - 将单元格值输入 excel 查询 Web 浏览器 URL

Question

我的问题:

通过New Query -> From Other Sources -> From Web ，我输入了一个静态 URL，它允许我从 JSON 格式的网页加载大约 6 万个“ID”。

我相信这些 ID 中的每一个都对应一个项目。

所以它们都被加载并组织在一个列中，每行一个 ID，在一个查询选项卡内。

暂时没有问题。

现在我需要从依赖于 ID 的动态 URL 导入信息。

所以我需要以这种形式从 URL 导入:

http://www.example.com/xxx/xxxx/ID

这将为每个 ID 导入以下内容:

对应项目名称，

平均价格，

供应,

需求，

等

经过研究，我得出的结论是，我必须使用查询编辑器中的“高级编辑器”来引用 ID 查询选项卡。

但是我不知道如何将静态部分与 ID 放在一起，以及如何在 60k 行中重复。

我试过这个:

let
    Source = Json.Document(Web.Contents("https://example.com/xx/xxxx/" & ID)),
    name1 = Source[name]
in
    name1

这将返回一个错误。

我认为这是因为我无法添加字符串和列。

问题:如何引用我感兴趣的单元格的值并将其添加到我的字符串中？

问题:我正在做的事情可行吗？

问题: Excel 将如何处理加载 60k 查询？

每个查询只需导入几个单词。

问题:是否可以通过一次查询从 60k 个不同的 URL 加载信息？

编辑 : 非常感谢 Alexis 的回答，非常有帮助。因此，为了避免复制您发布的内容，我没有使用该功能(告诉我您的想法):

let
    Source = Json.Document(Web.Contents("https://example.com/all-ID.json")),
    items1 = Source[items],
    #"Converted to Table" = Table.FromList(items1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "ID"}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns", "URL", each Text.Combine({"http://example.com/api/item/", Text.From([ID], "fr-FR")}), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Merged Column", "Item", each Json.Document(Web.Contents([URL]))),
    #"Expanded Item" = Table.ExpandRecordColumn(#"Added Custom", "Item", {"name"}, {"Item.name"})
in
    #"Expanded Item"

现在我遇到的问题是从所有 URL 加载我需要的所有信息需要很长时间。

事实证明，可以从 中提取多个 使用此格式的 ID 一次: http://example.com/api/item/ID1,ID2,ID3,ID4,...,IDN
我认为尝试从包含所有 ID 的 URL 一次加载是行不通的，因为 URL 包含太多无法处理的字符。

因此，为了加快速度，我现在要做的是将每第 N 行连接到一个单元格中，例如使用 N=3 :

205
651
320165
63156
4645
31
6351
561
561
31
35

会成为 :

205, 651, 320165
63156, 4645, 31
6351, 561, 561
31, 35

“分组依据”功能似乎不是我想要的，我不知道如何通过 Power Query 实现自动化

编辑 2

因此，经过大量测试，我找到了一个解决方案，即使它可能不是最优雅和最佳的:

我用 1 步创建了一个索引

我创建了另一个 costum 列，我将每 N 行与 N 增加的数字相关联

我使用“分组依据”->“所有行”来创建“计数”列

创建了一个服装栏“[Count][ID]

最后，我从该列中提取值并放置一个“，”分隔符

这是 N = 10 000 的代码:

let
    Source = Json.Document(Web.Contents("https://example.com/items.json")),
    items1 = Source[items],
    #"Converted to Table" = Table.FromList(items1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "ID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"ID", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if Number.RoundDown([Index]/10000) = [Index]/10000 then [Index] else Number.IntegerDivide([Index],10000)*10000),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Index", "ID", "Custom"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each [Count][ID]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

Answer 1

我认为您在这里要做的是创建一个自定义函数，您可以使用每个 ID 值调用该函数。

让我举一个类似的例子，它应该为你指明正确的方向。

假设我有一个名为 ListIDs 的表看起来像这样:

ID
----
1
2
3
4
5
6
7
8
9
10

对于每个 ID，我想从 Wikipedia 中提取一些信息(例如，对于 ID = 6，我想查找 https://en.wikipedia.org/wiki/6 并返回基数、序数、因式分解和 6 的除数)。

要获得一个 ID 值，我的查询将如下所示(再次使用 6):

let
    Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/6")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] = "Cardinal" or [Column2] = "Divisors" or [Column2] = "Factorization" or [Column2] = "Ordinal")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2", "Property"}, {"Column3", "Value"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Property]), "Property", "Value")
in
    #"Pivoted Column"

现在我们想把它转换成一个函数，这样我们就可以在不创建一堆查询的情况下多次使用它。 (注意:我也将此查询/函数命名为 WikiLookUp。)为此，请将其更改为以下内容:

let
    WikiLookUp = (ID as text) =>
let
    Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/" & ID)),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] = "Cardinal" or [Column2] = "Divisors" or [Column2] = "Factorization" or [Column2] = "Ordinal")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column2", "Property"}, {"Column3", "Value"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Property]), "Property", "Value")
in
    #"Pivoted Column"
in
    WikiLookUp

请注意，我们所做的只是将它包装在另一组 let...in 中。并定义参数 ID = text它被替换为 Source接近尾端的线。该函数应如下所示:



现在我们可以回到我们已经导入查询编辑器的表并在自定义列中调用我们新创建的函数。 (注意:请确保首先将您的 ID 值转换为文本类型，因为它们会附加到 URL。)

添加具有以下定义的自定义列(或使用调用自定义函数按钮)

= WikiLookUp([ID])

展开该列以引入您想要的所有列，您就完成了!

查询的 M 代码如下所示:

let
    Source = Excel.CurrentWorkbook(){[Name="ListIDs"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each WikiLookUp([ID])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Cardinal", "Ordinal", "Factorization", "Divisors"}, {"Cardinal", "Ordinal", "Factorization", "Divisors"})
in
    #"Expanded Custom"

查询应如下所示: