Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create an empty table when webpage table returns empty

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" ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago
    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?


    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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