Forum Discussion
How to handle a single error on web based table navigation
- 8 years ago
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"
The company that is failing, is it missing any data inside your data columns that you are building from? For example, if a particualr column was null that is expecting a value, would the string you are building not sufficiently work as a parameter?
- masplin8 years agoImpactful Individual
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
- Anonymous8 years agoNot applicable
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- masplin8 years agoImpactful Individual
Hi Lydia
I tried various ways to insert her code, but can't make it work as dont realy understand what it is doing. Step2 adds an extra columns called Custom9, but it just has an error on the rows where the previous Custom column had an error so I havent moved forward.
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])), #"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 Web_Description([Search])), Step2 = Table.AddColumn(#"Added Custom1", "Custom9", each if Value.Is([Custom], type table) then [Custom] else #table({"ConvertedText"}, {{[Custom]}})), #"Erweiterte Custom" = Table.ExpandTableColumn(Step2, "Custom9", Table.ColumnNames(Table.Combine(Step2[Custom])), Table.ColumnNames(Table.Combine(Step2[Custom]))), #"Expanded Custom1" = Table.ExpandTableColumn( #"Erweiterte Custom", "Custom", {"Description"}, {"Description"}),