hi!
I am using below code to pull data from an online website into my excel sheet using Excel Power query. The power query is automatically refreshed every few minutes using an Excel Macro.
The resulting table is a table of 2 rows, (one header row and another data row). The table structure is maintained in case the internet connection is working and the data is coming properly.
In case the internet connection is down and the data source is not available, I have 2 issues:
1. The Excel gives an error message and stops any other work, until I manually acknowledge the excel error messages.
- I managed to address this issue by wrapping the code in try let and otherwise 0;
2. If I used the code in serial no.1 above, the excel continues to operate and refreshes other data from other data sources, but the table structure for the specific website is converted into a single cell with value 0. This results in the #Ref! error for other cells, which were looking at the data in the above columns and rows. Also, the subsequent columns of that table are automatically deleted, even though I have selected the option of "Overwrite existing cells with new data, clear unused cells, in case of data refresh, in Table Properties.
Normal Table:
Null Table in case Data Source not available:
Request:
I need to modify the below code to ensure that even in case the data source is not available during the Query/ Table refresh, the table structure should not get broken. In case there is no source data during a refresh, the cells could be filled with 0. I have tried the powerquery error handling options such as MissingField.UseNull or MissingField.Ignore, but I have been unable to get them to work in my case.
I am using below code to pull data from an online website into my excel sheet using Excel Power query. The power query is automatically refreshed every few minutes using an Excel Macro.
The resulting table is a table of 2 rows, (one header row and another data row). The table structure is maintained in case the internet connection is working and the data is coming properly.
In case the internet connection is down and the data source is not available, I have 2 issues:
1. The Excel gives an error message and stops any other work, until I manually acknowledge the excel error messages.
- I managed to address this issue by wrapping the code in try let and otherwise 0;
2. If I used the code in serial no.1 above, the excel continues to operate and refreshes other data from other data sources, but the table structure for the specific website is converted into a single cell with value 0. This results in the #Ref! error for other cells, which were looking at the data in the above columns and rows. Also, the subsequent columns of that table are automatically deleted, even though I have selected the option of "Overwrite existing cells with new data, clear unused cells, in case of data refresh, in Table Properties.
Normal Table:
Null Table in case Data Source not available:
Request:
I need to modify the below code to ensure that even in case the data source is not available during the Query/ Table refresh, the table structure should not get broken. In case there is no source data during a refresh, the cells could be filled with 0. I have tried the powerquery error handling options such as MissingField.UseNull or MissingField.Ignore, but I have been unable to get them to work in my case.
Power Query:
try let
Source = Web.Page(Web.Contents("https://www.investing.com/commodities/real-time-futures")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"", type text}, {"Commodity", type text}, {"Month", type date}, {"Last", type number}, {"High", type number}, {"Low", type number}, {"Chg.", type number}, {"Chg. %", Percentage.Type}, {"Time", type date}, {"2", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",7),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",26),
#"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"", "Time", "2"})
in
#"Removed Columns"
otherwise 0
