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" = 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 

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

3 Replies

  • 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