Forum Discussion
How to handle a single error on web based table navigation
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
I've just left in a few examples where it errors and where it doesn't. Does this dropbox link work?
Thnaks
Mike
masplin，
When I import the Excel file into Power BI Desktop , I am unable to reproduce your issue as all nested table values are imported as Text values.
Regards,
Lydia
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"That's odd. I was using Power query in Excel but just imported it to desktop and see this so "error" not a null for top and bottom rows. Can you see any difference in the Desktop version I've attached CRO 2 Error PBIX? I'm doing this on my machine at work as wel las at home so don't think its machine related.
Thnaks
Mike
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
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