Need help with my excel file which have queries fetching data from Websites

[Anonymous]

I have 1 excel file having power queries which are fetching data from websites 7 getting refreshed every 5 mins.

but whenever any error pops up like (cannot fetch datadue to website error). further refresh stops.

I want some way so that any error for refreshing will be ignored & further refreshes will work.

[Anonymous]

Hi, Thanks for the input, 

but if something goes wrong & refresh is not successful then can it retain the last refreshed values ?

[Anonymous]

Refer below code from advanced editor for 1 query, similarly there are 4 different web based query

Can you please guide how to use this "remove error" "try otherwise"

let
Source = Excel.Workbook(Web.Contents("http://mesftvp8/ReportServer/Pages/ReportViewer.aspx?/VantagePointReports/SSRS_SHOPWISE_PRODUCTION_S..."), null, true),
SSRS_SHOPWISE_PRODUCTION_SUMMAR1 = Source{[Name="SSRS_SHOPWISE_PRODUCTION_SUMMAR"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(SSRS_SHOPWISE_PRODUCTION_SUMMAR1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}, {"Column86", type text}, {"Column87", type text}, {"Column88", type text}, {"Column89", type text}, {"Column90", type text}, {"Column91", type text}, {"Column92", type text}, {"Column93", type text}, {"Column94", type text}, {"Column95", type text}, {"Column96", type text}, {"Column97", type text}, {"Column98", type text}, {"Column99", type text}, {"Column100", type text}, {"Column101", type text}, {"Column102", type text}, {"Column103", type text}, {"Column104", type text}, {"Column105", type text}, {"Column106", type text}, {"Column107", type text}, {"Column108", type text}, {"Column109", type text}, {"Column110", type text}, {"Column111", type text}, {"Column112", type text}, {"Column113", type text}, {"Column114", type text}, {"Column115", type text}, {"Column116", type text}, {"Column117", type text}, {"Column118", type text}, {"Column119", type text}, {"Column120", type text}, {"Column121", type text}, {"Column122", type text}, {"Column123", type text}, {"Column124", type text}, {"Column125", type text}, {"Column126", type text}, {"Column127", type text}, {"Column128", type text}, {"Column129", type text}, {"Column130", type text}, {"Column131", type text}, {"Column132", type text}, {"Column133", type text}, {"Column134", type text}, {"Column135", type text}, {"Column136", type text}, {"Column137", type text}}),
#"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Blank Rows",27),
#"Transposed Table" = Table.Transpose(#"Removed Bottom Rows"),
#"Removed Columns" = Table.RemoveColumns(#"Transposed Table",{"Column17", "Column18", "Column19", "Column20", "Column21", "Column1", "Column2", "Column22", "Column15", "Column16"}),
#"Removed Blank Rows1" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Transposed Table1" = Table.Transpose(#"Removed Blank Rows1"),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table1",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Transposed Table2" = Table.Transpose(#"Merged Columns"),
#"Filled Up" = Table.FillUp(#"Transposed Table2",{"Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Column12] <> null)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column13", "Column14"}),
#"Merged Columns1" = Table.CombineColumns(#"Removed Columns1",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Promoted Headers" = Table.PromoteHeaders(#"Merged Columns1", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"* Live Export Data", type text}, {"BIW Start", Int64.Type}, {"BIW Out", Int64.Type}, {"G-Paint In", Int64.Type}, {"G-BT1100", Int64.Type}, {"G-Paint Out", Int64.Type}, {"K-Paint In", type text}, {"K-RB170", type text}, {"K-Paint Out", type text}, {"TCF Trim In", Int64.Type}, {"TCF Roll Out", Int64.Type}, {"BIW Start_1", Int64.Type}, {"BIW Out_2", Int64.Type}, {"G-Paint In_3", Int64.Type}, {"G-BT1100_4", Int64.Type}, {"G-Paint Out_5", Int64.Type}, {"K-Paint In_6", type text}, {"K-RB170_7", type text}, {"K-Paint Out_8", type text}, {"TCF Trim In_9", Int64.Type}, {"TCF Roll Out_10", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","-",0,Replacer.ReplaceValue,{"BIW Start", "BIW Out", "G-Paint In", "G-BT1100", "G-Paint Out", "K-Paint In", "K-RB170", "K-Paint Out", "TCF Trim In", "TCF Roll Out", "BIW Start_1", "BIW Out_2", "G-Paint In_3", "G-BT1100_4", "G-Paint Out_5", "K-Paint In_6", "K-RB170_7", "K-Paint Out_8", "TCF Trim In_9", "TCF Roll Out_10"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"BIW Start", Int64.Type}, {"BIW Out", Int64.Type}, {"G-Paint In", Int64.Type}, {"G-BT1100", Int64.Type}, {"G-Paint Out", Int64.Type}, {"K-Paint In", Int64.Type}, {"K-RB170", Int64.Type}, {"K-Paint Out", Int64.Type}, {"TCF Trim In", Int64.Type}, {"TCF Roll Out", Int64.Type}, {"BIW Start_1", Int64.Type}, {"BIW Out_2", Int64.Type}, {"G-Paint In_3", Int64.Type}, {"G-BT1100_4", Int64.Type}, {"G-Paint Out_5", Int64.Type}, {"K-Paint In_6", Int64.Type}, {"K-RB170_7", Int64.Type}, {"K-Paint Out_8", Int64.Type}, {"TCF Trim In_9", Int64.Type}, {"TCF Roll Out_10", Int64.Type}})
in
#"Changed Type2"

[KT_Bsmart2gethe]

Hi @Anonymous ,

Source = try Excel.Workbook(Web.Contents("http://mesftvp8/ReportServer/Pages/ReportViewer.aspx?/VantagePointReports/SSRS_SHOPWISE_PRODUCTION_S..."), null, true) otherwise "",

Have a go

Regards

KT

[KT_Bsmart2gethe]

Hi @Anonymous ,

Source = try Excel.Workbook(Web.Contents("http://mesftvp8/ReportServer/Pages/ReportViewer.aspx?/VantagePointReports/SSRS_SHOPWISE_PRODUCTION_S..."), null, true) otherwise #"Changed Type2",

......

#"Changed Type2" = try Table.TransformColumnTypes(#"Replaced Value",{{"BIW Start", Int64.Type}, {"BIW Out", Int64.Type}, {"G-Paint In", Int64.Type}, {"G-BT1100", Int64.Type}, {"G-Paint Out", Int64.Type}, {"K-Paint In", Int64.Type}, {"K-RB170", Int64.Type}, {"K-Paint Out", Int64.Type}, {"TCF Trim In", Int64.Type}, {"TCF Roll Out", Int64.Type}, {"BIW Start_1", Int64.Type}, {"BIW Out_2", Int64.Type}, {"G-Paint In_3", Int64.Type}, {"G-BT1100_4", Int64.Type}, {"G-Paint Out_5", Int64.Type}, {"K-Paint In_6", Int64.Type}, {"K-RB170_7", Int64.Type}, {"K-Paint Out_8", Int64.Type}, {"TCF Trim In_9", Int64.Type}, {"TCF Roll Out_10", Int64.Type}}) otherwise ""
in
#"Changed Type2"

if source is not reachable then go to last step. if last step is not fail then return "". This will give you an empty table.

Regards

KT

[KT_Bsmart2gethe]

Hi @Anonymous @Anonymous @

It depends on how you have the code written.  Here are a few advice:

1. Use remove error rows which can skip the error source.  This is based on you have the url structured in the way before drill down to the contents.

2. You can use try otherwise to point to other step where it allows the step to run through. E.g step 1 error. Add try step 1 otherwise step 2.

Regards

KT