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"
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
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,
LydiaHi 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