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 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"}),
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
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,
LydiaThat'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