Struggling with function ot handle a column that on some webpages is a table and sometimes a value

[masplin]

I have built a function to read the webpages of a website. however one field sometimes reutrns a 1 row table and sometimes a value.  i need some type of if statement that if it is a table to expand it/rename/change type but if it itsnt just change type. Is this possible?

This is is basically my function code put I feed in a list of values instead of "055". This errors because on this page RatingDate is a value

let
    Source = Xml.Tables(Web.Contents("http://ratings.food.gov.uk/OpenDataFiles/FHRS" & "055" &"en-GB.xml")),
    Table1 = Source{1}[Table],
    Table0 = Table1{0}[Table],
    #"Removed Other Columns" = Table.SelectColumns(Table0,{"FHRSID", "BusinessName", "BusinessType", "RatingValue", "RatingDate", "LocalAuthorityName"}),
    #"Expanded RatingDate" = Table.ExpandTableColumn(#"Removed Other Columns", "RatingDate", {"Element:Text"}, {"Element:Text"}),
    #"Renamed Columns" = Tablelet
    Source = Xml.Tables(Web.Contents("http://ratings.food.gov.uk/OpenDataFiles/FHRS" & "055" &"en-GB.xml")),
    Table1 = Source{1}[Table],
    Table0 = Table1{0}[Table],
    #"Removed Other Columns" = Table.SelectColumns(Table0,{"FHRSID", "BusinessName", "BusinessType", "RatingValue", "RatingDate", "LocalAuthorityName"}),
    #"Expanded RatingDate" = Table.ExpandTableColumn(#"Removed Other Columns", "RatingDate", {"Element:Text"}, {"Element:Text"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded RatingDate",{{"Element:Text", "RatingDate"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each Date.IsInPreviousNYears([RatingDate], 2)),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"FHRSID", "BusinessName", "BusinessType", "RatingValue", "LocalAuthorityName"})
in
    #"Removed Other Columns1".RenameColumns(#"Expanded RatingDate",{{"Element:Text", "RatingDate"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each Date.IsInPreviousNYears([RatingDate], 2)),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"FHRSID", "BusinessName", "BusinessType", "RatingValue", "LocalAuthorityName"})
in
    #"Removed Other Columns1"

If you change 055 to 021 it works fine as RatingDate is a table. On the RemoveOther Columns line you can see the difference clearly.  So instead of just having the Expand RatingDate code I need some test to only do it if it is actually a Table

Thanks for any help

Mike

[v-jiascu-msft · Accepted solution]

Hi Mike,

I would suggest you try the formula below. Please check out the demo in the attachment.

let
    myfunc = (p1 as text) => 
let
    Source = Xml.Tables(Web.Contents("http://ratings.food.gov.uk/OpenDataFiles/FHRS" & p1 &"en-GB.xml")),
    Table1 = Source{1}[Table],
    Table0 = Table1{0}[Table],
    #"Removed Other Columns" = Table.SelectColumns(Table0,{"FHRSID", "BusinessName", "BusinessType", "RatingValue", "RatingDate", "LocalAuthorityName"}),
    #"Expanded RatingDate" = if Value.Type(#"Removed Other Columns"[RatingDate]) = List.Type then Table.ExpandTableColumn(#"Removed Other Columns","RatingDate", {"Element:Text"}, {"Element:Text"}) else Table.RenameColumns(#"Removed Other Columns",{{ "RatingDate", "Element:Text"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded RatingDate",{{"Element:Text", "RatingDate"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each Date.IsInPreviousNYears([RatingDate], 2)),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"FHRSID", "BusinessName", "BusinessType", "RatingValue", "LocalAuthorityName"})
 
in
    #"Removed Other Columns1"
in
    myfunc

Best Regards,
Dale

[masplin]

That's brilliant thanks

I actually reduced the code a bit as can do the renaming in the expanded RatingDate row

(Page as text ) as table =>

let
    Source = Xml.Tables(Web.Contents("http://ratings.food.gov.uk/OpenDataFiles/FHRS" & Page &"en-GB.xml")),
    Table1 = Source{1}[Table],
    Table0 = Table1{0}[Table],
    #"Removed Other Columns" = Table.SelectColumns(Table0,{"FHRSID", "BusinessName", "BusinessType", "RatingValue", "RatingDate", "LocalAuthorityName"}),
    #"Expanded RatingDate" = if Value.Type(#"Removed Other Columns"[RatingDate]) = List.Type then Table.ExpandTableColumn(#"Removed Other Columns","RatingDate", {"Element:Text"}, {"RatingDate"}) else "RatingDate",
   #"Changed Type" = Table.TransformColumnTypes(#"Expanded RatingDate",{{"RatingDate", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInPreviousNYears([RatingDate], 2)),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"FHRSID", "BusinessName", "BusinessType", "RatingValue", "LocalAuthorityName"})
in
    #"Removed Other Columns1"

 Out of interest you put let before the function and "in my func" at the end. does this make any difference to the execution?

Much appreciate your help

Mike

[v-jiascu-msft]

Hi Mike,

My pleasure.

The "let in" doesn't make any difference. Your code could go wrong if the "else" statement is active. It should be a table expression.

else "RatingDate",

else Table.RenameColumns(#"Removed Other Columns",{{ "RatingDate", "Element:Text"}}),
// why change name in this step. Because the following code can be static without change.

Best Regards,
Dale

[masplin]

Actually my code doesn't work for the case where the column isnt a table?