CODEWITHSUNDEEP
powerbipowerquerym
Rudy
Rudy

Reputation: 17

How to combine multiple sources during the creation of a 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.

Upvotes: 1

Views: 1239

Answers (1)

Marcus
Marcus

Reputation: 3995

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

Upvotes: 1

Related Questions