Create an empty table when webpage table returns empty

[Anonymous]

Hi All,

Looking for some help, Here is the current state of things 

let
    Source = Web.Page(Web.Contents("INSERT WEB TABLE ADDRESS HERE")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", type text}, {"Column9", type text}, {"Column10", Int64.Type}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type datetime}}), otherwise
     Table.IsEmpty(Source)= false then Table.TransformColumnTypes(Source, {{"Name",type any},{"Data",type any},{"Signature",type any}}) else Source
in
    #"Changed Type"

When there is no data in the table, I get an error and the table and anything attached to it fails to load as there is no data, how would i go about having this create a empty table with the colum headers indicated above 

[v-shex-msft]

HI @Anonymous,

You can try to use 'try otherwise' keywords to trace request and use if statement to package result steps:

let
    Source = try Web.Page(Web.Contents('your url')) otherwise null,
    Result= if Source=null or Table.IsEmpty(Source) then #table(1,{}) else Source
in
    Result

Regards,

Xiaoxin SHeng

[Anonymous]

let
    Source = try Web.Page(Web.Contents(website goes here")) otherwise null,
    Result= if Source = null or Table.IsEmpty(Source) then #"Table 0 (2)"(1,{}) else Source,
    Data0 = Source{0}[Data], 

    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type datetime}})
    in
    #"Changed Type"

still getting errors when trying to refresh data and theres nothing within the table 

"Expression.Error: The column 'Column1' of the table wasn't found.
Details:
Column1"

ideas?

[v-shex-msft · Accepted solution]

HI @Anonymous ,

I think it should related to the power query cache feature. (Power query will cache table structure into query steps) 

For this scenario, you need to modify empty table data structure (same as the cached data structure) to fix specific columns not exist issues.

In addition, your formula seems like direct invoke original 'source' steps instead of replaced 'result' steps. I modify your formula and merge your 'change type' steps into 'replace' steps.

let
    Source = try Web.Page(Web.Contents("website goes here")) otherwise null,
    Result= 
    if Source = null or Table.IsEmpty(Source) 
    then #table(type table[#"Column1"=  text, #"Column2"= number, #"Column3"=  text, #"Column4"=  text, #"Column5"= number, #"Column6"= number, #"Column7"= number, #"Column8"= number, #"Column9"=  text, #"Column10"=  text, #"Column11"=  text, #"Column12"= number, #"Column13"=  datetime],{}) 
    else Table.TransformColumnTypes(Source{0}[Data],{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type text}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", type datetime}})
    in
    Result

BTW, #table is a function used to define a table, it not means I invoked other query tables.

Creating Tables In Power BI/Power Query M Code Using #table() 

Regards,

Xiaoxin Sheng