如何在创建查询时合并多个来源?

huangapple go评论114阅读模式
英文:

How to combine multiple sources during the creation of a query?

问题

我想创建一个查询,它可以同时加载多个数据源,格式化它们,然后将它们合并。

我使用管道操作符 ' |> ' 来格式化这些数据源,以简化整个过程。

let
    Source1 = Json.Document(Web.Contents(...))
        |> Table.FromRecords
        |> Table.RemoveColumns({"...", "..."})
        |> Table.ExpandListColumn(...)
        |> Table.ExpandRecordColumn("...", {"...", "...", "..."});

    Source2 = Json.Document(Web.Contents(...))
        |> Table.FromRecords
        |> Table.RemoveColumns({"...", "..."})
        |> Table.ExpandListColumn(...)
        |> Table.ExpandRecordColumn("...", {"...", "...", "..."});

    Combined = Table.Combine({Source1, Source2})

in
    Combined

Power Query 告诉我在第一个管道操作符 ' | ' 的前面缺少逗号。如果我添加逗号,它会说缺少标识符。

我可以创建多个请求然后将它们合并,但我想避免这样做,因为我的工作站非常慢,这样做会使它变得更加缓慢,因为有太多的依赖关系。

英文:

I want to create a query in which it loads multiple sources at a time, formats them and then combines them.

I used pipeline operators '|>' to format the sources and to simplify the process.

let
Source1 = Json.Document(Web.Contents(...))
    |> Table.FromRecords
    |> Table.RemoveColumns({"...", "..."})
    |> Table.ExpandListColumn(...)
    |> Table.ExpandRecordColumn("...", {"...", "...", "..."});

Source2 = Json.Document(Web.Contents(...))
    |> Table.FromRecords
    |> Table.RemoveColumns({"...", "..."})
    |> Table.ExpandListColumn(...)
    |> Table.ExpandRecordColumn("...", {"...", "...", "..."});

Combined = Table.Combine({Source1, Source2})

in
Combined

Power Query tells me that a comma is missing at the first '|' of the first operator. If I add one, it says that an Identifier is missing.

I can create multiple requests and then combine them, but I want to avoid this because my workstation is very slow and it will slow it down even more because of all the dependencies.

答案1

得分: 1

编辑:Power Query 不支持此表示法。您需要使用标准查询语法:

let
  Source1 = Json.Document(Web.Contents(...)),
  s11 = Table.FromRecords(Source1),
  s12 = Table.RemoveColumns(s11, {"...", "..."}),
  s13 = Table.ExpandListColumn(s12, ...),
  Source1T = Table.ExpandRecordColumn(s13, "...", {"...", "...", "..."}),
  Source2 = Json.Document(Web.Contents(...)),
  s21 = Table.FromRecords(Source2),
  s22 = Table.RemoveColumns(s21, {"...", "..."}),
  s23 = Table.ExpandListColumn(s22, ...),
  Source2T = Table.ExpandRecordColumn(s23, "...", {"...", "...", "..."}),
  Combined = Table.Combine({Source1T, Source2T})
in
  Combined

或者我认为类似这样的内容也可以工作:

let
  Source1 = 
    let 
      s10 = Json.Document(Web.Contents(...)),
      s11 = Table.FromRecords(s10),
      s12 = Table.RemoveColumns(s11, {"...", "..."}),
      s13 = Table.ExpandListColumn(s12, ...)
    in
      Table.ExpandRecordColumn(s13, "...", {"...", "...", "..."}),
  
  Source2 = 
    let 
      s20 = Json.Document(Web.Contents(...)),
      s21 = Table.FromRecords(s20),
      s22 = Table.RemoveColumns(s21, {"...", "..."}),
      s23 = Table.ExpandListColumn(s22, ...)
    in
      Table.ExpandRecordColumn(s23, "...", {"...", "...", "..."}),
  
  Combined = Table.Combine({Source1, Source2})
in
  Combined
英文:

Edit: This notation is not supported by Power Query. You will need to use standard query syntax:

let
  Source1 = Json.Document(Web.Contents(...)),
  s11 = Table.FromRecords(Source1),
  s12 = Table.RemoveColumns(s11, {"...", "..."}),
  s13 = Table.ExpandListColumn(s12, ...),
  Source1T = Table.ExpandRecordColumn(s13, "...", {"...", "...", "..."}),
  Source2 = Json.Document(Web.Contents(...)),
  s21 = Table.FromRecords(Source2),
  s22 = Table.RemoveColumns(s21, {"...", "..."}),
  s23 = Table.ExpandListColumn(s22, ...),
  Source2T = Table.ExpandRecordColumn(s23, "...", {"...", "...", "..."}),
  Combined = Table.Combine({Source1T, Source2T})
in
  Combined

or I suppose something like this would also work:

let
  Source1 = 
    let 
      s10 = Json.Document(Web.Contents(...)),
      s11 = Table.FromRecords(Source1),
      s12 = Table.RemoveColumns(s11, {"...", "..."}),
      s13 = Table.ExpandListColumn(s12, ...)
    in
      Table.ExpandRecordColumn(s13, "...", {"...", "...", "..."}),
  
  Source2 = 
    let 
      s20 = Json.Document(Web.Contents(...)),
      s21 = Table.FromRecords(Source2),
      s22 = Table.RemoveColumns(s21, {"...", "..."}),
      s23 = Table.ExpandListColumn(s22, ...)
    in
      Table.ExpandRecordColumn(s23, "...", {"...", "...", "..."}),
  
  Combined = Table.Combine({Source1T, Source2T})
in
  Combined

huangapple
  • 本文由 发表于 2023年2月8日 21:50:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/75386767.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定