Excel power query with empty table

[serhito]

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"

[lbendlin]

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]

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]

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

[serhito]

I appreciate your help.

As I mentionned, I have no coding experience, so I wouldn't know where to place what you suggested.

I decided to go with googlesheet. It gives more flexibility,  and no error for the task that I want to do.

[lbendlin]

where do you get that message? in Data2?

[serhito]

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