none
How to Append an empty table RRS feed

  • Question

  • Hello,

    let's say I need to import two tables from the Web and then append the second to the first one. Very easy, but sometimes there are too few data in total. In such a case I get, let's say, Table1 with a certain number of records, but not an empty table as  Table2. In such a case there is no Table2 at all, and power query generates an error. How is it possible to avoid this error and import the records of Table1 anyway? Here there are two links as example:

    https://www.google.com/finance/historical?q=EPA:ELIS&startdate=01/03/2005&enddate=10/28/2015&num=200&ei=u3AoVvjaKMaDsgHb4YyQCw&start=0

    https://www.google.com/finance/historical?q=EPA:ELIS&startdate=01/03/2005&enddate=10/28/2015&num=200&ei=u3AoVvjaKMaDsgHb4YyQCw&start=200

    Thanks in advance,

    Franco

    Wednesday, October 28, 2015 12:51 PM

Answers

  • Hello Franco

    Error raised because there are NO that table at all, not "empty" table.

    I mean, with first URL you got a query like this (if you don't mess with editor):

    let
        Source = Web.Page(Web.Contents("https://www.google.com/finance/historical?q=EPA:ELIS&startdate=01/03/2005&enddate=10/28/2015&num=200&ei=u3AoVvjaKMaDsgHb4YyQCw&start=0")),
        Data = Source{1}[Data]
    in
        Data

    You can see that result of the 1st step is a table of 3 rows, you drill to table in second row and got your data.

    But when you try to load 2nd URL, there no such table like in 1st step on first URL, there are only two tables, and table in 2nd row is different. That's why there is an error when you try to get into the data.

    So you need to perform a filtering of sources before drill into table, and then add error handling with "try/otherwise":

    let
        Source = Web.Page(Web.Contents("https://www.google.com/finance/historical?q=EPA:ELIS&startdate=01/03/2005&enddate=10/28/2015&num=200&ei=u3AoVvjaKMaDsgHb4YyQCw&start=200")),
        FilterTable = Table.SelectRows(Source, each ([ClassName] = "gf-table historical_price")),
        Data = try FilterTable{0}[Data] otherwise Table.FromColumns({{null},{null},{null},{null},{null},{null}},{"Date","Open","High","Low","Close","Volume"})
    in
        Data

    And after appending tables don't forget to remover errors and nulls after appending:

    let
        Combined= Table.Combine({#"Table 1",#"Table 0"}),
        ChType = Table.TransformColumnTypes(Combined,{{"Date", type date}}),
        RemErrs = Table.RemoveRowsWithErrors(ChType, {"Date"}),
        FilterNulls = Table.SelectRows(RemErrs, each ([Date] <> null))
    in
        FilterNulls


    Maxim Zelensky Excel Inside

    • Marked as answer by Franco Fornari Thursday, October 29, 2015 7:51 AM
    Thursday, October 29, 2015 7:01 AM

All replies

  • Check if the 2nd table is null in M code and if so, don't perform the Append.
    Wednesday, October 28, 2015 1:49 PM
  • Hi Greg,

    I looked for a proper function, but found only Table.IsEmpty. What else I can use to check if the table is null?

    Thanks,

    Franco

    Wednesday, October 28, 2015 2:28 PM
  • Try this:

    = Table.Combine(List.RemoveNulls({table1, table2}))

    Ehren

    Wednesday, October 28, 2015 10:38 PM
    Owner
  • Hello Franco

    Error raised because there are NO that table at all, not "empty" table.

    I mean, with first URL you got a query like this (if you don't mess with editor):

    let
        Source = Web.Page(Web.Contents("https://www.google.com/finance/historical?q=EPA:ELIS&startdate=01/03/2005&enddate=10/28/2015&num=200&ei=u3AoVvjaKMaDsgHb4YyQCw&start=0")),
        Data = Source{1}[Data]
    in
        Data

    You can see that result of the 1st step is a table of 3 rows, you drill to table in second row and got your data.

    But when you try to load 2nd URL, there no such table like in 1st step on first URL, there are only two tables, and table in 2nd row is different. That's why there is an error when you try to get into the data.

    So you need to perform a filtering of sources before drill into table, and then add error handling with "try/otherwise":

    let
        Source = Web.Page(Web.Contents("https://www.google.com/finance/historical?q=EPA:ELIS&startdate=01/03/2005&enddate=10/28/2015&num=200&ei=u3AoVvjaKMaDsgHb4YyQCw&start=200")),
        FilterTable = Table.SelectRows(Source, each ([ClassName] = "gf-table historical_price")),
        Data = try FilterTable{0}[Data] otherwise Table.FromColumns({{null},{null},{null},{null},{null},{null}},{"Date","Open","High","Low","Close","Volume"})
    in
        Data

    And after appending tables don't forget to remover errors and nulls after appending:

    let
        Combined= Table.Combine({#"Table 1",#"Table 0"}),
        ChType = Table.TransformColumnTypes(Combined,{{"Date", type date}}),
        RemErrs = Table.RemoveRowsWithErrors(ChType, {"Date"}),
        FilterNulls = Table.SelectRows(RemErrs, each ([Date] <> null))
    in
        FilterNulls


    Maxim Zelensky Excel Inside

    • Marked as answer by Franco Fornari Thursday, October 29, 2015 7:51 AM
    Thursday, October 29, 2015 7:01 AM
  • Hello Maxim,

    this is exactly what I was looking for. In my message I wrote tha same as you say: there is no second table at all. But I didn't mind to use Table.SelectRows on the Source. All is clear now.

    Thank you very much,

    Franco

    Thursday, October 29, 2015 7:28 AM
  • Glad to help, Franco.

    Good luck


    Maxim Zelensky Excel Inside

    Thursday, October 29, 2015 9:09 AM