Forum Discussion
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 were slightly different for different parts of the data i wanted.
This all works great apart from one company . The first function is fine, but the 2nd function is giving the error " There weren't enough elements in the enumeration to complete the operation." It seems there just aren't as many navigation levels on this item as all the others.
So my first choice is just to elimiate the whole row, but is there any way to keep the row with the data retruned by the other function and replace the error with a null or something? Essentially trying to expand the table from the 2nd function blows up, but just for one row
This is my code with thr Added.Custom1 being the issue on expansion
let
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])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "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 Web_Description([Search])),
#"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Company", Order.Ascending}})
in
#"Sorted Rows"Thanks
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"
11 Replies
So here is an example webpage. One function pulls out the data on the left and the other finds the description which is in a different part of the tree. This ne has no description so errors
https://app.scientist.com/providers/labor-zollinger-ag
whereas this one does not
https://app.scientist.com/providers/kinematica
My function for the description looks like
(company as text) as table => let Source = Web.Page(Web.Contents("https://app.scientist.com/providers/" & company)), Data0 = Source{0}[Data], Children0 = Data0{0}[Children], Children2 = Children0{2}[Children], Children7 = Children2{7}[Children], Children3 = Children7{3}[Children], Children1 = Children3{1}[Children], Children4 = Children1{1}[Children], Children5 = Children4{1}[Children], Children6 = Children5{3}[Children], Children8 = Children6{1}[Children], Children9 = Children8{3}[Children], Children = Children9{3}[Children], Children10 = Children{1}[Children], Children11 = Children10{1}[Children], #"Removed Other Columns" = Table.SelectColumns(Children11,{"Text"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Text", "Description"}}), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Description] <> null)), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each 0), #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Table", each _, type table}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Description", each Table.Column([Table],"Description")), #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Description", each Text.Combine(List.Transform(_, Text.From)), type text}), #"Removed Other Columns1" = Table.SelectColumns(#"Extracted Values",{"Description"}) in #"Removed Other Columns1"I'm new to using this chidlren approach to extracting data off a webpage, but I assume I'm essentially navigating down an html tree to find the field. Presumably the one that errors just doesn't have a tree of this size.
When I add the custom column to cal lthis function i see this. So I need somethnig to turn this table error inot a blank or somethnig so the rest of the tables can expand. I can delete the whole row, but that throws away the whole company.
I saw an idea about using a dummy table so tried this but didnt work in the main query. Presumable the table isnt empty, just failing.
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 if Table.IsEmpty(Web_Description([Search])) then DummyTable else Web_Description([Search])), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom1", {"Custom"}), #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Errors", "Custom", {"Description"}, {"Description"}) in #"Expanded Custom1"Thnaks
Mike
masplin,
You can add another new column that checks if the content of the current row of the custom column to expand is of type table. For more details, please review ImkeF's reply in the following similar thread.
https://community.powerbi.com/t5/Desktop/Expand-column-where-not-all-records-are-tables/td-p/79060
Regards,
Lydia
