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"
masplin,
Is there any possibilty that you can share the Excel file to me? I will test it in my Power BI Desktop.
Regards,
Lydia
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
- Anonymous8 years agoNot applicable
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- masplin8 years agoImpactful Individual
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
- masplin8 years agoImpactful Individual
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