Forum Discussion
Solved
How to handle a single error on web based table navigation
I have have built two functions to call data from a web based source using a list of 200 different url parts. The url basically just ends in a company name. I needed 2 functions as the html paths wer...
Hi Mike, I've changed the step "Added Custom1", pls try it out:
let DummyTable = Table.PromoteHeaders(Table.FromRows({{"Description", "Index"}})), Source = Excel.Workbook(File.Contents("U:\Dropbox\Limerston\Crawford\CRO Companies.xlsx"), null, true), CRO_Company_Table = Source{[Item="CRO_Company",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(CRO_Company_Table,{{"Company", type text}, {"Address", type text}, {"Search", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Web_Main([Search])), #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Company", Order.Ascending}}), #"Expanded Custom" = Table.ExpandTableColumn(#"Sorted Rows1", "Custom", {"#(lf)Website#(lf)", "#(lf)Headquarters#(lf)", "#(lf)Laboratories#(lf)", "#(lf)Company Type#(lf)", "#(lf)Certifications#(lf)", "#(lf)Year Established#(lf)", "#(lf)No. of Employees#(lf)"}, {"#(lf)Website#(lf)", "#(lf)Headquarters#(lf)", "#(lf)Laboratories#(lf)", "#(lf)Company Type#(lf)", "#(lf)Certifications#(lf)", "#(lf)Year Established#(lf)", "#(lf)No. of Employees#(lf)"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each try Web_Description([Search]) otherwise DummyTable), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"Custom"}), #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Errors", "Custom", {"Description"}, {"Description"}) in #"Expanded Custom1"
Hi Lydia wondered if you had any idea why i see differnetly to you. I am now on another project and have the same problem again this timne where i need ot pul ldown 6 seperate tables for each record, but only some tables in some records are empty. This means I have to throw away the whole record as unable ot handle the rror on the missing table.
rEally appreciate any help as can see this being an ongoing problem
Mike
Hi Mike, I've changed the step "Added Custom1", pls try it out:
let
DummyTable = Table.PromoteHeaders(Table.FromRows({{"Description", "Index"}})),
Source = Excel.Workbook(File.Contents("U:\Dropbox\Limerston\Crawford\CRO Companies.xlsx"), null, true),
CRO_Company_Table = Source{[Item="CRO_Company",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(CRO_Company_Table,{{"Company", type text}, {"Address", type text}, {"Search", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Web_Main([Search])),
#"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Company", Order.Ascending}}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Sorted Rows1", "Custom", {"#(lf)Website#(lf)", "#(lf)Headquarters#(lf)", "#(lf)Laboratories#(lf)", "#(lf)Company Type#(lf)", "#(lf)Certifications#(lf)", "#(lf)Year Established#(lf)", "#(lf)No. of Employees#(lf)"}, {"#(lf)Website#(lf)", "#(lf)Headquarters#(lf)", "#(lf)Laboratories#(lf)", "#(lf)Company Type#(lf)", "#(lf)Certifications#(lf)", "#(lf)Year Established#(lf)", "#(lf)No. of Employees#(lf)"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each try Web_Description([Search]) otherwise DummyTable),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"Custom"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Errors", "Custom", {"Description"}, {"Description"})
in
#"Expanded Custom1"
Sorry it took me so long to reply to this but got pulled off onto another project. Thank you so much as that sorted it out perfectly
Mike