Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
masplin
Impactful Individual
Impactful Individual

How to handle a single error on web based table navigation

I have have built two functions to call data from a web based source using a list of 200 different url parts. The url basically just ends in a company name. I needed 2 functions as the html paths were slightly different for different parts of the data i wanted. 

 

This all works great apart from one company  . The first function is fine, but the 2nd function is giving the error " There weren't enough elements in the enumeration to complete the operation."  It seems there just aren't as many navigation levels on this item as all the others. 

 

So my first choice is just to elimiate the whole row, but is there any way to keep the row with the data retruned by the other function and replace the error with a null or something?  Essentially trying to expand the table from the 2nd function blows up, but just for one row

 

This is my code with thr Added.Custom1 being the issue on expansion

 

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])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "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])),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Company", Order.Ascending}})
in
    #"Sorted Rows"

Thanks

Mike.

1 ACCEPTED SOLUTION

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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?

masplin
Impactful Individual
Impactful 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. 

 

Capture.PNG

 

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

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

CRO 2 Error

@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.
1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

Capture.PNG

masplin
Impactful Individual
Impactful 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

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

masplin
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors