Forum Discussion

serhito's avatar
serhito
Regular Visitor
6 years ago

Excel power query with empty table

I have a daily power query request to extract a table off a website. Some days, the table is just empty. When I update, I get a message : "Expression error. The column 'Ticker' of the table wasn't found". I just need to return an empty table but avoiding the popup message. Any idea ?

Tha

 

 

 

let
    Source = Web.Page(Web.Contents("https://finviz.com/screener.ashx?v=151&f=cap_midover,earningsdate_tomorrowafter,geo_usa&ft=4&o=-marketcap")),
    Data2 = Source{2}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Ticker", type text}, {"Company", type text}, {"Sector", type text}, {"Industry", type text}, {"Country", type text}, {"Market Cap", type text}, {"P/S", type number}, {"P/B", type number}, {"P/FCF", type number}, {"Dividend", type text}, {"EPS", type number}, {"EPS next Y", Percentage.Type}, {"EPS next 5Y", Percentage.Type}, {"ROA", Percentage.Type}, {"Debt/Eq", type number}, {"Profit M", Percentage.Type}, {"Earnings", type text}, {"Target Price", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sector", "Industry", "Country", "P/S", "P/B", "P/FCF", "Dividend", "EPS", "EPS next Y", "EPS next 5Y", "ROA", "Debt/Eq", "Profit M", "Target Price"})
in
    #"Removed Columns"

 

 

 

 

 

Need Help

6 Replies

    • serhito's avatar
      serhito
      Regular Visitor
      6 years ago

      I just get a popup message when updating the table. But only when the table is empty.

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    encapsulate your 

    Table.TransformColumnTypes

    line with a try...otherwise... construct, or check the returned size of Data2, and then only do the rest of the processing if it is non-zero.

     

    In the grand scheme of things you need to worry much more about why that table is sometimes empty. Unstable web server? timing issue?

    • serhito's avatar
      serhito
      Regular Visitor
      6 years ago

      it's returning a calendar table of stocks due for earning release. The table is empty when there are no stocks due that day.

      If I understand what you are saying, I should add something that will check if the table is empty, then stop if it is, then otherwise proceed with the rest. The problem is, I have no experience coding. If it's just a line, would you mind helping me here ? Would save me lots of time trying to figure it out, and would be well appreciated. Or at least point me out to which instruction I would need to look for.

      Thanks so much for your reply.

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        You can just wrap your last step in the query with try ... otherwise null.  

         

        let

        Source = ...

        #"Change Type" = try ... otherwise null

        in

        #"Change Type"

         

        Note that may cause errors downstream if that query is used in other queries, or if you have DAX calculated column(s).  If that is the case, you can make a query that has some mock data that matches the format of your real data.  In your Source step, you can use a Source = try Web.Contents(...) otherwise mockdataquery

         

        That way all the downstream stuff will still work.

         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat